Power Query Dynamic Column Reference

Question

The answer to this question is either the answer to my question or a close runner up. My main problem is I almost totally do not understand the syntax of the answer.

I have multiple weekly dates that run for a year or two as my column headers. However as time marches on some columns will be added and others will disappear. Sometime the number of dates will also vary. So like the linked question I need to know how to reference all my date columns without actually knowing what they are called as their names will change which will cause my query to blow up.

In this example of my power query I am trying to change the format of each of the date columns to decimal.

= Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text},_
 {"Project No", type text}, {"Phase No", type text}, {"Project Name", type text},_
 {"Phase Name", type text}, {"Office", type text}, {"Staff", type text},_
 {"2024/09/09", type number}, {"2024/09/16", type number}, {"2024/09/23", type number},_
 {"2024/09/30", type number}, {"2024/10/07", type number}, {"2024/10/14", type number},_
 {"2024/10/21", type number}, {"2024/10/28", type number}, {"2024/11/04", type number},_
 {"2024/11/11", type number}, {"2024/11/18", type number}, {"2024/11/25", type number},_
 {"2024/12/02", type number}, {"2024/12/09", type number}, {"2024/12/16", type number},_
 {"2024/12/23", type number}, {"2024/12/30", type number}, {"2025/01/06", type number},_
 {"2025/01/13", type number}, {"2025/01/20", type number}, {"2025/01/27", type number},_
 {"2025/02/03", type number}, {"2025/02/10", type number}, {"2025/02/17", type number},_
 {"2025/02/24", type number}, {"2025/03/03", type number}, {"2025/03/10", type number},_
 {"2025/03/17", type number}, {"2025/03/24", type number}, {"2025/03/31", type number},_
 {"2025/04/07", type number}, {"2025/04/14", type number}, {"2025/04/21", type number},_
 {"2025/04/28", type number}, {"2025/05/05", type number}, {"2025/05/12", type number},_
 {"2025/05/19", type number}, {"2025/05/26", type number}, {"2025/06/02", type number},_
 {"2025/06/09", type number}})

I had to manually pick each column and change it. Royal pain in the butt. I would like this to be an automated process where I do not need to change the name of the columns every time, because a date has been added or removed.

If the answer to the linked question is also the answer to this question, can the syntax/ answer be explained for a first time user of power query and data bases?

Answer 1

I'm probably missing something here but just define the list of columns you don't want to be changed to decimal and do your transformations:

let
    Source = whatever

    NonDateColumns = {"Source.Name", "Project No", "Phase No", "Project Name", "Phase Name", "Office", "Staff"},
    AllColumns = Table.ColumnNames(Source),
    DateColumns = List.Difference(AllColumns, NonDateColumns),
    TransformedTable = Table.TransformColumnTypes(Source, List.Transform(DateColumns, each {_, type number}))

in
    TransformedTable