Mass Data Editing Query/Data Modelling

[dcadwallader]

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?

[GilbertQ]

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]

Hi @GilbertQ

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

It's simply that wide.

Cheers for getting back though.

[v-shex-msft]

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]

Super @v-shex-msft! 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-an...)

[v-shex-msft]

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.

Regards,

Xiaoxin Sheng

[dcadwallader]

Hi @v-shex-msft 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.

[v-shex-msft]

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