Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vidyasagar159
Helper II
Helper II

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
1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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.

StartStartAdd custom columnAdd custom column

fill downfill 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

 

 

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

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.

StartStartAdd custom columnAdd custom column

fill downfill 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

 

 

@jdbuchanan71 

 

This is exactly what I am looking for thank you so much for your help.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.