cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Data Type by row

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 .

 

b

 

as you see i would need every second row shown as 89% ... etc

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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.

Specializing in Power Query Formula Language (M)
7 REPLIES 7
smpa01 Established Member
Established Member

Re: Data Type by row

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.

 

Capture.PNGRaw Data

Capture.PNGSolution Snapshot

 

Re: Data Type by row

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 ?

Thanks
Super User
Super User

Re: Data Type by row

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"
Specializing in Power Query Formula Language (M)

Re: Data Type by row

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

Super User
Super User

Re: 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.

Specializing in Power Query Formula Language (M)

Re: Data Type by row

Hi Marcel,

 

Wow you are amazing ... at the moment i am getting this error :

 

Capture2.PNG

 

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

Super User
Super User

Re: Data Type by row

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

Remove Change Type step.png

 

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.

Specializing in Power Query Formula Language (M)