Forum Discussion
Data Type by row
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.
Raw Data
Solution Snapshot
- Thanks smpa01 for the reply..
The data in the second table looks ok .. but the issue is that i have to present percentages on the first table aswell .. not 0.1 etc...
Any ideas if there is a way around it ?
ThanksMy 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
