need help with M query to unpivot

[parry2k]

Hi there,

I have data in excel sheet whcih I want unpivot, see attached link for sample file. anyhelp will be appreciated.

https://drive.google.com/file/d/0B4dPgH9_BPBrU0p3OVFCMkxpYVk/view?usp=sharing

thanks in advance,

p

[GilbertQ]

Hi there, that can possibly be done, but there would be quite a few steps involved where you would need to possibly transpose, then fill data down and up, transpose it back and then unpivot.

Or in some instances you would need to know which data is in which section and then create new columns based on the area's.

The great thing is that in the query editor you could try different ways, until you get it into the format you require.

[MarcelBeug · Accepted solution]

This is one way to do it.

Remark: lately I have been exploring conversions between lists, records and tables in Power Query.

This may have influenced the solution

let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],

    // 1. Transpose and pivot right part of the table
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Removed Columns1" = Table.RemoveColumns(#"Transposed Table",{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1),
    #"Integer Divided" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), type number}}),
    #"Merged Columns" = Table.CombineColumns(#"Integer Divided",{"Column3", "Column4", "Column5", "Column6"}, each _, "Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Column2]), "Column2", "Merged"),
    Zipped = Table.CombineColumns(#"Pivoted Column",{"Accounts", "Invoices", "Monthly Billing", "Balance"}, each List.Zip(_),"Zipped"),

    // 2. Fill down first 2 columns:
    #"Removed Top Rows" = Table.Skip(Source,2),
    #"Filled Down1" = Table.FillDown(#"Removed Top Rows",{"Column1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Down1",{"Column1", "Column2"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Other Columns",{"Column1", "Column2"},each Record.FromList(_,{"Type", "Category"}),"TypeCat"),

    // 3. List with clubs:
    Clubs = List.Select(Record.ToList(Source{0}), each _ <> null),

    // 4. Combine Clubs with part1 and expand:
    Custom2 = Table.FromColumns({Clubs,Zipped[Zipped]}),
    #"Expanded Column2" = Table.ExpandListColumn(Custom2, "Column2"),
    
    // 5. Repeat first 2 columns:
    Custom3 = Table.Repeat(#"Merged Columns1",Table.RowCount(#"Expanded Column2")/Table.RowCount(#"Merged Columns1")),

    // 6. Combine all parts and expand:
    Custom4 = Table.FromColumns({Custom3[TypeCat],Table.ToRecords(#"Expanded Column2")}),
    #"Expanded Column1" = Table.ExpandRecordColumn(Custom4, "Column1", {"Type", "Category"}, {"Type", "Category"}),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"Column1", "Column2"}, {"Column1", "Column2.1"}),

    // 7. Create columns from nested lists:
    RecordFromList = Table.TransformColumns(#"Expanded Column3",{{"Column2.1", each Record.FromList(_,{"Accounts", "Invoices", "Monthly Billing", "Balance"})}}),
    #"Expanded Column2.1" = Table.ExpandRecordColumn(RecordFromList, "Column2.1", {"Accounts", "Invoices", "Monthly Billing", "Balance"}, {"Accounts", "Invoices", "Monthly Billing", "Balance"}),

    // 8. Finishing touches.
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column2.1",{{"Column1", "Club"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Type", type text}, {"Category", type text}, {"Club", type text}, {"Accounts", type any}, {"Invoices", Int64.Type}, {"Monthly Billing", Currency.Type}, {"Balance", Currency.Type}})
in
    #"Changed Type"

[MarcelBeug]

Previous post corrected.

[parry2k]

Thanks @MarcelBeug

I'm going to try this, I was also doing this on my end using lists/records and able to develop something but running bit slow. Going to look at your solution.