Power BI Repeating Headers in a Column

[RupertBear]

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

[Zubair_Muhammad · Accepted solution]

@RupertBear 

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"