Power BI Data title header should append to rows using transformation

[vidyasagar159]

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
Phones	Category
Iphone6	No Demand
Iphone7	Demand
Iphone8	Demand
Iphone9	No Demand
Iphone10	Demand
Iphone11	Demand
Public Account
Phones	Category
Iphone6	No Demand
Iphone7	Demand
Iphone8	Demand
Iphone9	No Demand
Iphone10	Demand
Iphone11	Demand

Expected Result
Phones - Personal Account	Category
Iphone6 - Personal Account	No Demand
Iphone7 - Personal Account	Demand
Iphone8 - Personal Account	Demand
Iphone9 - Personal Account	No Demand
Iphone10 - Personal Account	Demand
Iphone11 - Personal Account	Demand
Phones - Public Account	Category
Iphone6 - Public Account	No Demand
Iphone7 - Public Account	Demand
Iphone8 - Public Account	Demand
Iphone9 - Public Account	No Demand
Iphone10 - Public Account	Demand
Iphone11 - Public Account	Demand

[jdbuchanan71 · Accepted solution]

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

[vidyasagar159]

@jdbuchanan71 

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