Forum Discussion
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. ...
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
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
