Power Query - Change Type Dynamically

[gelu]

Hello there,

I would appreciate your help on the following.

Detect data type dynamically.

I deal with changing file formats and tables sizes.
When detecting data types I would like to tell PQ to take whatever columns are there and deal with each of them accordingly (instead of hard coded column names as it does by default).

What I do:

1 - I load one file to PQ from folder (there is always just one file but it could be .txt .csv .xls .xlsx or whatever else).

2 - I keep generic column headings when combining the files (by selecting "Do not detect data types"

3 - In the example below the table has 9 columns. But next time it could have 3 or 25 ...

let
    Source = Csv.Document(Parameter1,[Delimiter="#(tab)", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, _
    {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, _
    {"Column7", type text}, {"Column8", type text}, {"Column9", type text}})
in
    #"Change Type"

How to do this dynamically?

 

[RoryA]

I tried it directly on a text file and it didn't do anything because the data was all already imported as text, not as any.

 

[gelu]

Here is a solution: Dynamically Detect Column Types with Power M

 

[gelu]

How did you apply it? Once you have a table, it doesn't matter how it got into PQ, it's just a table.

a diagnostic here: https://stackoverflow.com/questions...ically?noredirect=1#comment137369833_77918042