Forum Discussion

dcadwallader's avatar
dcadwallader
Helper 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 etc...)

 

Within the excel file, all columns are formatted accordingly; for example the figures in the pass rate columns are formatted as percentages as opposed to number or general.

 

When linked with Power BI, the whole numbers (inspected, passed) are picked up and formatted as such by Power BI.

 

This is not the case for the pass rate or fail rate. This is not picked up as either a decimal nor a percentage but instead as text.

 

This, as you're aware has an impact on how the information is presented in a graph.

 

I opened the Query Editor and adjusted the formatting here; this was handy enough as it allowed me to select multiple columns at one time.

 

However, having hit "Close & Apply" the formatting does not carry through. Instead, the data remains formatted as a number (so I see "1" as opposed to "84%".

 

I can format the relevant columns to percentage by going to the data tab (top left just below report tab) but can only do one column at a time - bear in mind this table is 1590 columns wides, so this really isn't a desirable option.

 

Anything I can do to speed up the operation?

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi dcadwallader,

     

    You can try to use below formula which about replace the column type:

     

    Custom functions:

     

    Replace text to type

    let 
    ConvertType = (input) => 
    let
        values = {
        {"text", type text},
        {"number", type number},
        {"date", type date},
        {input, type text}
        },
    Result = List.First(List.Select(values, each _{0}=input)){1}
in
    Result
in 
    ConvertType

     

    Replace structure:

    let 
    GetStruct = (sourceTable as table, ColumnNamelist as list, ReplaceList as list) =>
     let
        Source = List.Transform(Table.ToList(Table.SelectColumns(Table.Schema(sourceTable),{"Name","Kind"})), each Text.Split(_,",")),
        Change = List.Transform(Source, each {List.First(_), ConvertType(List.Last(_))}),
        Replace= List.Transform(Change, each if List.Contains(ColumnNamelist,List.First(_)) then List.ReplaceMatchingItems(_,ReplaceList) else _)
    in
        Replace
in 
    GetStruct

     

    Use Sample:

     

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

     

     

    Regards,

    Xiaoxin Sheng

    • ImkeF's avatar
      ImkeF
      Community 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

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi dcadwallader,

     

    I am unsure exactly of your data structure, but if you had to Unpivot the data, would you then not go from 1590 columns to possibly 3 - 4 columns?

     

    And then you could simply update it from there?

    • dcadwallader's avatar
      dcadwallader
      Helper I
      9 years ago

      Hi GilbertQ

       

      Would love that to be the case but the table isnt pivoted.

       

      It's simply that wide.

       

      Cheers for getting back though.