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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.