Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dcadwallader
Helper I
Helper I

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 7
GilbertQ
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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...)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.Smiley Happy

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.