Switch column data to row but each columns have two field names

[Syndicate_Admin]

Help please! I have data structured as per the "Original data" tab in the attached file.

Each column data has two field names, one with date, such as Jan 23, the other quantity or profit, such as 45. How can I switch the data so that the dates are displayed as rows but quantity and profit are displayed as columns please (see the "Desired structure" tab). Can this be done with power BI somehow?

Thanks!

[spinfuzer]

You can do this with the GUI.

1) Demote headers if necessary.  The headers should be Column1, Column2, ...

2)  Merge Column 1 and 2 with ";" separator.

3) Transpose

4) Promote Headers

5) Unpivot Other Columns

6) Split Attribute by ";"

7) Pivot on Attribute.2 and Value

😎Rename Columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZVbaxsxEIX/SvDzPug2ujy6LqEptGmb5snkwQ1uCaR2cNeF/PtKZ482smMKC6aFzBnNWhp9O6NVlsvZrCt/71ebrJfrb1k/rHZZ5087jJ/Lr/sN9LE83//IerN+ynp932f9uP2d9e36/mx57rrlbLH/1W9/rsvixXa/6Xclwef9atM/9P91+Gm3/f7Q/4tBKcNclypd3359dzG//HK1mOdQqyrGlNH4yBZJdWQQ+iKuyDAPE4fpZlgEdVCBemiARmjCZlhr8toR7KpfPT5f5IGnYWPFINZdY2VIDPxBuvIeUKAYoBigGKAYoBigGKBYrLUtyu1NFkdLpChj4Z7C54H7u4M8WcFgwWDBYMFgwWDBYMFgweCw1pHhjXndJzSBOyvGlrFtKWjuvDRjczSt9sbQ7JGvJmOurOBw4HDgcOBw4HDgcOBw4BjWSsuBzujG1FGsuW8be+TxYPBg8GDwYPBg8GDwYPBg8GAIWBv+0hk10cL4VllBI6AR0AhoBDQCGgGNgOblTXCt2ePO1A7Us+p5RiPP6/Ctoi5lajxbYYCC5kxEwEeekCvhpwSOBI4EjgSOBI4EjgSOBA7dKUXfspz4fKYQDbccUwfMCAALAAsACwALAAsACwALABuKG0/2qp7QesfYxmrsOW8g0WO2rCCJIIkgiSCJIIkgiSCJIHkp7+tWaRZFjgiEPjY0ujMs9XDTZm/oLb2jF3pPH+gjPVtnmW+8fU+0rr1xzJHVZ66Ja9ZzHaf5Qeemfu26c00+eNZMsWaKNVOsmWLNFGumWDNFPv7b7XTLh35OZxPmErIJ2YRsQjYhm5BNyCZkE7J55sMNdfcH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{";Customer", type text}, {";Country", type text}, {"Jan;Quantity", Int64.Type}, {"Feb;Quantity", Int64.Type}, {"Mar;Quantity", Int64.Type}, {"Apr;Quantity", Int64.Type}, {"May;Quantity", Int64.Type}, {"Jun;Quantity", Int64.Type}, {"Jul;Quantity", Int64.Type}, {"Aug;Quantity", Int64.Type}, {"Sep;Quantity", Int64.Type}, {"Oct;Quantity", Int64.Type}, {"Nov;Quantity", Int64.Type}, {"Dec;Quantity", Int64.Type}, {"Jan;Profit", Int64.Type}, {"Feb;Profit", Int64.Type}, {"Mar;Profit", Int64.Type}, {"Apr;Profit", Int64.Type}, {"May;Profit", Int64.Type}, {"Jun;Profit", Int64.Type}, {"Jul;Profit", Int64.Type}, {"Aug;Profit", Int64.Type}, {"Sep;Profit", Int64.Type}, {"Oct;Profit", Int64.Type}, {"Nov;Profit", Int64.Type}, {"Dec;Profit", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {";Customer", ";Country"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

[jennratten]

Hello - this is how you can do it.  The example below shows the last step in Power Query as pivoting the profit/quantity back to columns, but a better solution (in my opinion) would be to load the data to the data model without the last (pivot) step and then use the Attribute column in a visual.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZVbaxsxEIX/SvDzPug2ujy6LqEptGmb5snkwQ1uCaR2cNeF/PtKZ482smMKC6aFzBnNWhp9O6NVlsvZrCt/71ebrJfrb1k/rHZZ5087jJ/Lr/sN9LE83//IerN+ynp932f9uP2d9e36/mx57rrlbLH/1W9/rsvixXa/6Xclwef9atM/9P91+Gm3/f7Q/4tBKcNclypd3359dzG//HK1mOdQqyrGlNH4yBZJdWQQ+iKuyDAPE4fpZlgEdVCBemiARmjCZlhr8toR7KpfPT5f5IGnYWPFINZdY2VIDPxBuvIeUKAYoBigGKAYoBigGKBYrLUtyu1NFkdLpChj4Z7C54H7u4M8WcFgwWDBYMFgwWDBYMFgweCw1pHhjXndJzSBOyvGlrFtKWjuvDRjczSt9sbQ7JGvJmOurOBw4HDgcOBw4HDgcOBw4BjWSsuBzujG1FGsuW8be+TxYPBg8GDwYPBg8GDwYPBg8GAIWBv+0hk10cL4VllBI6AR0AhoBDQCGgGNgOblTXCt2ePO1A7Us+p5RiPP6/Ctoi5lajxbYYCC5kxEwEeekCvhpwSOBI4EjgSOBI4EjgSOBA7dKUXfspz4fKYQDbccUwfMCAALAAsACwALAAsACwALABuKG0/2qp7QesfYxmrsOW8g0WO2rCCJIIkgiSCJIIkgiSCJIHkp7+tWaRZFjgiEPjY0ujMs9XDTZm/oLb2jF3pPH+gjPVtnmW+8fU+0rr1xzJHVZ66Ja9ZzHaf5Qeemfu26c00+eNZMsWaKNVOsmWLNFGumWDNFPv7b7XTLh35OZxPmErIJ2YRsQjYhm5BNyCZkE7J55sMNdfcH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t]),
    TableToRows = List.Zip ( { Table.ToRows ( Source ){0}, Table.ToRows ( Source ){1} } ),
    CombineHeaders = List.Transform ( TableToRows, each Text.Combine ( { _{0}, _{1} }, "|" ) ),
    NewHeaders = Record.FromList ( CombineHeaders, Table.ColumnNames ( Source ) ),
    RemoveTopRows = Table.Skip(Source,2),
    InsertNewHeaders = Table.InsertRows ( RemoveTopRows, 0, {NewHeaders} ),
    #"Replaced Value" = Table.ReplaceValue(InsertNewHeaders,"|","",Replacer.ReplaceText,{"Column1", "Column2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Customer", "Country"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Month", "Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"