Forum Discussion

vidyasagar159's avatar
vidyasagar159
Icon for Helper II rankHelper II
5 years ago
Solved

Power BI Data title header should append to rows using transformation

Hello Everyone,

 

I have an excel data set in the below format. But I want to convert the same in the expected format below as well. Is there a way to achieve this in Power BI transformation. 

 

Personal Account 
PhonesCategory
Iphone6No Demand
Iphone7Demand
Iphone8Demand
Iphone9No Demand
Iphone10Demand
Iphone11Demand
Public Account 
PhonesCategory
Iphone6No Demand
Iphone7Demand
Iphone8Demand
Iphone9No Demand
Iphone10Demand
Iphone11Demand

 

Expected Result 
  
Phones - Personal AccountCategory
Iphone6 - Personal AccountNo Demand
Iphone7 - Personal AccountDemand
Iphone8 - Personal AccountDemand
Iphone9 - Personal AccountNo Demand
Iphone10 - Personal AccountDemand
Iphone11 - Personal AccountDemand
Phones - Public AccountCategory
Iphone6 - Public AccountNo Demand
Iphone7 - Public AccountDemand
Iphone8 - Public AccountDemand
Iphone9 - Public AccountNo Demand
Iphone10 - Public AccountDemand
Iphone11 - Public AccountDemand
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    In PowerQuery you can add a custom column that checks if column 2 is blank and if so get the value from column 1 then to a fill down.

    StartAdd custom column

    fill down

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PS8xRcExOzi/NK1HSUVJQitUBSmTk56UWA7nOiSWp6flFlWBRzwKQsBlQ2C9fwSU1NzEvBUncHCiOIWiBTdAShwmGBthUGxqiigaUJuVkJg8dJ8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.Length(Text.Trim([Column2])) = 0 then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom.1"})
in
    #"Filled Down"

     

    This is the code for the custom column.

    if Text.Length(Text.Trim([Column2])) = 0 then [Column1] else null

     

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    In PowerQuery you can add a custom column that checks if column 2 is blank and if so get the value from column 1 then to a fill down.

    StartAdd custom column

    fill down

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PS8xRcExOzi/NK1HSUVJQitUBSmTk56UWA7nOiSWp6flFlWBRzwKQsBlQ2C9fwSU1NzEvBUncHCiOIWiBTdAShwmGBthUGxqiigaUJuVkJg8dJ8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.Length(Text.Trim([Column2])) = 0 then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom.1"})
in
    #"Filled Down"

     

    This is the code for the custom column.

    if Text.Length(Text.Trim([Column2])) = 0 then [Column1] else null