Power Query Breaks When Column Name Is Referenced At Changed Type

[wsnyder]

Hi all,

My Power Query breaks when I update to a new file and the new file does not contain a Column Name that is hard-coded in the Query from the original file used to create the Query.
I promoted the headers and noticed that many columns had the incorrect DataType (ABC|123) so I went through each column and updated the DataType.
There are 63 Columns in total (before UnPivot Step)
Of 63 Columns, 60 are Decimal Number, and 3 are Text
The 60 Columns follow this naming convention: Scenario_MonthName_Year: BGT_Jan_2023
Scenario prefixes: BGT, ACT, EST

How can I get the correct Data Type on the respective Columns without referring to the Columns by name so my query does not break in future iterations when various columns are added or deleted?

Thanks,
-w

 

[GraH]

Do not do the change type step before the unpivot. Changes are you need to define less types.
Alternative is to use a construction with Table.ColumnNames(PreviousStepName) to fetch the headers dynamically. That gives a list that can be transformed, like
List.Transform(Headers, each {_, type text} )
to define all as text. But depending on the headers it might be possible to distinct dates, numbers, durations, text. Since only 3 are text, you can do something like
List.Transform(List.Difference(Headers, {"ColAsText1", "ColAsText2", "ColAsText3"}), each {_, type number} )

 

[jdellasala]

My general rule of thumb is to never do a Change Type until as late as possible in the query unless absolutely needed, and then only for the columns that require it like for math operations. By default the PQ Editor is set to automatically add a Change Type step after loading data. This can be turned off by going to Data -> Get Data -> Query Options and under Global for Data Load, set Type Detection to Never detect column types and headers for unstructured sources.

 

[wsnyder]

Thanks G & Jerry,

@G - I'll have to take the dynamic list idea as a takeaway to try out later.

@jerry - I updated the Options to Never detect data types - thanks for that.

In my original query, I had 2 Steps that were hard-coding the column names. The first was to ReOrder the Columns, The Second was to Change the Data Types

ReOrder Columns - I removed this step. Turn out it is not needed the final UnPivot put all of the columns in the correct order.
Change Data Type - As @G mentioned, I moved this to the final step after UnPivot so there are only 5-6 text columns and only 1 column with decimal numbers.

Thanks for your good help.
-w