cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## need help with M query to unpivot

Hi there,

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

p

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

1 ACCEPTED SOLUTION
Community Champion

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"}),
#"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"```
Specializing in Power Query Formula Language (M)
4 REPLIES 4
Super User

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.

Proud to be a Super User!

Power BI Blog

Community Champion

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"}),
#"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"```
Specializing in Power Query Formula Language (M)
Community Champion

Previous post corrected.

Specializing in Power Query Formula Language (M)
Super User

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.