New Member
Posts: 1
Registered: Wednesday

Mass Data Editing Query/Data Modelling



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?

Super Contributor
Posts: 949
Registered: ‎08-14-2016

Re: Mass Data Editing Query/Data Modelling

Hi @dcadwallader,


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


Custom functions:


Replace text to type

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


Replace structure:

    GetStruct = (sourceTable as table, ColumnNamelist as list, ReplaceList as list) =>
        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 _)


Use Sample:


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




Xiaoxin Sheng

Established Member
Posts: 202
Registered: ‎06-24-2015

Re: Mass Data Editing Query/Data Modelling

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?

Twitter: @guavaq
New Contributor
Posts: 622
Registered: ‎09-06-2015

Re: Mass Data Editing Query/Data Modelling

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:


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


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:

Imke Feldmann

The code above is a complete query which you can follow step-by-step if you copy it into the Advanced Editor: Edit queries -> Home -> New Query -> New Source -> Blank Query -> Query -> Advanced Editor: Check all content and replace by the code above.
Super Contributor
Posts: 949
Registered: ‎08-14-2016

Re: Mass Data Editing Query/Data Modelling

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



Xiaoxin Sheng