Forum Discussion
Solved
Power BI Repeating Headers in a Column
Hi,
I am trying to format my data into a useable table, With the headings (Headings1-10) at the top only repeated once and the corresponding values underneath
i.e.
Heading 1 Heading 2
Value 1 Value2
Value 11 Value12
Value 21 ...,
as you would normally find. I can't work out exactly how to manipulate these columns to get this.
Best regards
Try this
Please also see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc89CoAwGIPhq0jnDk1r/bmBJ3ApHQRFBHHz/jp9JUO28GR6S3HLse3Xc3Zw3q3b/R7/qr55NI/kyTyR9+Y9eTbP5IP5QD6aj+ST+UQ+m8/kCC0s8NMOlQzVDBUNVQ2VDdUNFQ5VDpke//T6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Repeated Headings" = _t, Values = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Repeated Headings", type text}, {"Values", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]-Number.Mod([Index],List.Count(List.Distinct(#"Changed Type"[Repeated Headings])))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Repeated Headings"]), "Repeated Headings", "Values"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}) in #"Removed Columns1"
1 Reply
- Zubair_Muhammad
Community Champion
Try this
Please also see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc89CoAwGIPhq0jnDk1r/bmBJ3ApHQRFBHHz/jp9JUO28GR6S3HLse3Xc3Zw3q3b/R7/qr55NI/kyTyR9+Y9eTbP5IP5QD6aj+ST+UQ+m8/kCC0s8NMOlQzVDBUNVQ2VDdUNFQ5VDpke//T6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Repeated Headings" = _t, Values = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Repeated Headings", type text}, {"Values", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]-Number.Mod([Index],List.Count(List.Distinct(#"Changed Type"[Repeated Headings])))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Repeated Headings"]), "Repeated Headings", "Values"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}) in #"Removed Columns1"