Forum Discussion

jPinhao's avatar
jPinhao
Advocate II
10 years ago

Detect type of column in Power Query Formula Language

I've got some query tables generated from imported data. All the data comes in as type 'Any', and I'm trying to automatically detect the type of the data in each column.   Some of the queries gener...
Need Help
sminonese's avatar
sminonese
Frequent Visitor
9 years ago

This should be an idea, as the current implementation requires you to specify the column names. Adding a new column to a table means you need to update the "Change data type" step manually. There should be a "detect data type on all columns, exluding some" step so even automatically added columns will automatically get an auto-detected data type.

 

Alternatively, you could think about unpivoting your data so you sort the data according to data types. One column for quantitative values, one column for text values, and one column for the "Attribute" name. Then it's only a matter of adding rows to the table, rather than adding columns.

 

 

  • thakks's avatar
    thakks
    Helper I
    9 years ago

    sminonese jPinhao were you guys able to work this out. Same issue i have come across where i have to detect the data type of new columns and convert accordingly.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      You can select the applicable columns and choose "Detect Data Type" on the Transform tab.

       

       

      • thakks's avatar
        thakks
        Helper I
        8 years ago

        I have to detect dynamically, when new columns are picked up while importing the csv file. I wish if it was that simple.