Forum Discussion

dcadwallader's avatar
dcadwallader
Icon for Helper I rankHelper I
9 years ago

Mass Data Editing Query/Data Modelling

Afternoon,   So, I have a Power BI report linked to an excel file.   This excel file charts performance statistics for various stakeholders across different metrics (inspected, passed, pass rate ...
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

Super Anonymous! Dynamic type conversion based on existing types - very useful!

If my understanding is correct, for this example the formula has to be slightly adjusted:

 

let
    Source =Table.TransformColumnTypes(TableName, GetStruct(TableName, {"Column1","Column2","Column3"}, {{type text, type number}}))
in
    Source

 

However, this will convert all columns that come in as text to a number format (and throw errors where this is not possible).

 

So another way would be to use a command that takes a list of column names as an input, who shall be converted to a specific format:

 

Table.TransformColumnTypes(TableName, List.Transform(ListOfColumnNames, each {_, type number}))

This will convert every column whose name is in the ListOfColumnNames into type number, irrespective of their current type.

 

So a completely different approach and suitable for different use cases. (See: http://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m/)

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi ImkeF,


Thanks for your link imkeF.

 

>>However, this will convert all columns that come in as text to a number format (and throw errors where this is not possible).

The comment of the function: GetStruct(table, choosed column name list, convert type list)

 

The second parameter is the filtered list. The formula will check it first.  The last paramter support mutiple type, for example:
{{type date, type text},{type text, type number},...}

 

BTW, I try to manual write one because I haven't found the related information yet.:smileyhappy:

 

Regards,

Xiaoxin Sheng

  • dcadwallader's avatar
    dcadwallader
    Icon for Helper I rankHelper I
    9 years ago

    Hi Anonymous and ImkeF,

     

    Sorry but I am very new to this whole thing.

     

    If I understand your proposed solution correctly, this is a formula which I use within my report which will adjust the formatting on the desired columns?

    This sounds great - one thing (and don't laugh) where do I put that formula?

     

    Many thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi dcadwallader,

       

      >>This sounds great - one thing (and don't laugh) where do I put that formula?

      These are power query formulas, you can open the query editor and find out the queries, open the advanced editor panel to modify them.

       

      Regards,

      Xiaoxin Sheng