Forum Discussion

alexbalazsalex's avatar
alexbalazsalex
Helper IV
8 years ago
Solved

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

 

 

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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.

7 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    8 years ago

    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

     

    • alexbalazsalex's avatar
      alexbalazsalex
      Helper IV
      8 years ago
      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
      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        8 years ago

        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"