Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I would need your suport if possible, in case i have a table where every second row is a percentage... is there any way to change the data type to percentage ? so i would have a mixed column .
as you see i would need every second row shown as 89% ... etc
Solved! Go to Solution.
My solution would require some typing as you need to provide formulas for each column.
It looks like in your latest data, the first 3 columns shouildn't be converted ("Sub_BU", "Column1" and "Values").
So my proposal would be a dynamic solution. With the code below, all numbers in rows 2, 4, 6, from the 4th column onwards, will be converted to textual percentages and all numbers in rows 1, 3, 5, etcetera, from the 4th column onwards, will be converted to text.
So a prerequisite is that, after your filter step, the percentages are in the even row numbers, and the numbers in the uneven row numbers.
Just replace your code with the code below.
let Source = Excel.Workbook(File.Contents("C:\Users\60060109\Desktop\tutorial\OTD Support for slide up to Nov17.xlsx"), null, true), OTD_Table = Source{[Item="OTD",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(OTD_Table,{{"Sub_BU", type text}, {"Column1", Int64.Type}, {"Values", type text}, {"Jul16", type number}, {"Aug16", type number}, {"Sep16", type number}, {"Oct16", type number}, {"Nov16", type number}, {"Dec16", type number}, {"Jan17", type number}, {"Feb17", type number}, {"Mar17", type number}, {"Apr17", type number}, {"May17", type number}, {"Jun17", type number}, {"Acc. 16/17", type number}, {"Jul17", type number}, {"Aug17", type number}, {"Sep17", type number}, {"Oct17", type number}, {"Nov17", type number}, {"Dec17", type any}, {"Jan18", type any}, {"Feb18", type any}, {"Mar18", type any}, {"Apr18", type any}, {"May18", type any}, {"Jun18", type any}, {"Acc. 17/18", type number}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Sub_BU] <> "COPI")), TransformOperations = List.Transform(List.Skip(Table.ColumnNames(#"Filtered Rows"),3),each {_, each Number.ToText(_,"P1"), type text}), typeTransformations = List.Transform(TransformOperations, each {_{0}, _{2}}), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Removed Alternate Rows" = Table.AlternateRows(#"Added Index",0,1,1), Percentages = Table.TransformColumns(#"Removed Alternate Rows", TransformOperations), #"Removed Alternate Rows1" = Table.AlternateRows(#"Added Index",1,1,1), Numbers = Table.TransformColumnTypes(#"Removed Alternate Rows1",typeTransformations), NumbersAndPercentages = Numbers & Percentages, #"Sorted Rows" = Table.Sort(NumbersAndPercentages,{{"Index", Order.Ascending}}) in #"Sorted Rows"
Should you have additional questions, then please share a link to a file with representative sample data.
I may be completely wrong but what I know from using Power BI in my day-to-day work that values in a single column can't have different data type. However, I managed to solve your problem in a different way. Please refer to the screenshot and let me know if it is of any use to you. I will share the code.
My suggestion would be to reconsider the layout and have 1 data type in 1 column.
Otherwise the only workaround I can think of, is to convert all values to text.
In the query below the table is split, based on alternate rows, and converted to text.
For percentages, Number.ToText(_,"P1") is used for percentages with 1 decimal (P2 for 2 decimals, etc,).
Next the table is combined and sorted back, using the Index that was first added.
let Source = #table(type table[Values = text, #"acc 16/17" = number, #"17-Jul" = number], {{"No. Items", 100, 200}, {"OTD (%)", 0.1, 0.2}, {"No. Items", 100, 200}, {"OTD (%)", 0.1, 0.2}}), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Removed Alternate Rows" = Table.AlternateRows(#"Added Index",0,1,1), Percentages = Table.TransformColumns(#"Removed Alternate Rows", {{"acc 16/17", each Number.ToText(_,"P1"), type text}, {"17-Jul", each Number.ToText(_,"P1"), type text}}), #"Removed Alternate Rows1" = Table.AlternateRows(#"Added Index",1,1,1), Numbers = Table.TransformColumnTypes(#"Removed Alternate Rows1",{{"17-Jul", type text}, {"acc 16/17", type text}}), NumbersAndPercentages = Numbers & Percentages, #"Sorted Rows" = Table.Sort(NumbersAndPercentages,{{"Index", Order.Ascending}}) in #"Sorted Rows"
Hi Marcel thanks for the feedback..
as i m new to this .. can you advise what i have to change in my query to get the reulst you told me as i cant figure it out
let
Source = Excel.Workbook(File.Contents("C:\Users\60060109\Desktop\tutorial\OTD Support for slide up to Nov17.xlsx"), null, true),
OTD_Table = Source{[Item="OTD",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(OTD_Table,{{"Sub_BU", type text}, {"Column1", Int64.Type}, {"Values", type text}, {"Jul16", type number}, {"Aug16", type number}, {"Sep16", type number}, {"Oct16", type number}, {"Nov16", type number}, {"Dec16", type number}, {"Jan17", type number}, {"Feb17", type number}, {"Mar17", type number}, {"Apr17", type number}, {"May17", type number}, {"Jun17", type number}, {"Acc. 16/17", type number}, {"Jul17", type number}, {"Aug17", type number}, {"Sep17", type number}, {"Oct17", type number}, {"Nov17", type number}, {"Dec17", type any}, {"Jan18", type any}, {"Feb18", type any}, {"Mar18", type any}, {"Apr18", type any}, {"May18", type any}, {"Jun18", type any}, {"Acc. 17/18", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Sub_BU] <> "COPI"))
in
#"Filtered Rows"
MAny Thanks
My solution would require some typing as you need to provide formulas for each column.
It looks like in your latest data, the first 3 columns shouildn't be converted ("Sub_BU", "Column1" and "Values").
So my proposal would be a dynamic solution. With the code below, all numbers in rows 2, 4, 6, from the 4th column onwards, will be converted to textual percentages and all numbers in rows 1, 3, 5, etcetera, from the 4th column onwards, will be converted to text.
So a prerequisite is that, after your filter step, the percentages are in the even row numbers, and the numbers in the uneven row numbers.
Just replace your code with the code below.
let Source = Excel.Workbook(File.Contents("C:\Users\60060109\Desktop\tutorial\OTD Support for slide up to Nov17.xlsx"), null, true), OTD_Table = Source{[Item="OTD",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(OTD_Table,{{"Sub_BU", type text}, {"Column1", Int64.Type}, {"Values", type text}, {"Jul16", type number}, {"Aug16", type number}, {"Sep16", type number}, {"Oct16", type number}, {"Nov16", type number}, {"Dec16", type number}, {"Jan17", type number}, {"Feb17", type number}, {"Mar17", type number}, {"Apr17", type number}, {"May17", type number}, {"Jun17", type number}, {"Acc. 16/17", type number}, {"Jul17", type number}, {"Aug17", type number}, {"Sep17", type number}, {"Oct17", type number}, {"Nov17", type number}, {"Dec17", type any}, {"Jan18", type any}, {"Feb18", type any}, {"Mar18", type any}, {"Apr18", type any}, {"May18", type any}, {"Jun18", type any}, {"Acc. 17/18", type number}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Sub_BU] <> "COPI")), TransformOperations = List.Transform(List.Skip(Table.ColumnNames(#"Filtered Rows"),3),each {_, each Number.ToText(_,"P1"), type text}), typeTransformations = List.Transform(TransformOperations, each {_{0}, _{2}}), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Removed Alternate Rows" = Table.AlternateRows(#"Added Index",0,1,1), Percentages = Table.TransformColumns(#"Removed Alternate Rows", TransformOperations), #"Removed Alternate Rows1" = Table.AlternateRows(#"Added Index",1,1,1), Numbers = Table.TransformColumnTypes(#"Removed Alternate Rows1",typeTransformations), NumbersAndPercentages = Numbers & Percentages, #"Sorted Rows" = Table.Sort(NumbersAndPercentages,{{"Index", Order.Ascending}}) in #"Sorted Rows"
Should you have additional questions, then please share a link to a file with representative sample data.
Hi Marcel,
Wow you are amazing ... at the moment i am getting this error :
Also could you recommend some good courses on this ? i m starting to gain interest in power BI .. do you have a website or a chanell??
many thanks,
ALex
The error must be in the #"Changed Type" from your part of the code.
My suggestion would be to remove the Changed Type step (from the "Applied Steps" pane: click the red cross):
With regard to courses: Sometimes I publish video's about specific Power Query subjects on my YouTube channel and I recently published a Power Query (M) Functions dashboard.
Otherwise you can find learning resources on http://www.thebiccountant.com/learning-resources/
You should be aware that the code I provided as a solution, is far beyond the basics.
Also for this reason, you might want to reconsider another table structure with 1 data type in each column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |