Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, hoping someone may be able to help me.
I have a dataset that has 5 columns of items and quantities i.e. item 1, quantity 1, item 2, quantity 2, item 3, quantity 3 etc.
I have tried various ways to transpose columns to rows and also Unpivot within Power Query but I am unable to get it right.
Original table:
What I am looking for:
Someone on another forum has attempted to help me but I haven't been able to achieve what I am looking for: https://www.mrexcel.com/board/threads/transpose-or-unpivot.1165557/#post-5661218
Thanks
Solved! Go to Solution.
Hi @Trebor84
You could try below codes. I have added some comments in it to make it clearer.
let
Source = Excel.Workbook(File.Contents("D:\TestData\Book - Copy.xlsx"), null, true),
Table5_Table = Source{[Item="Table5",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table5_Table,{{"Date", type date}, {"Contract", type text}, {"Job Ref", Int64.Type}, {"Model", type text}, {"Contractor", type text}, {"Acc No", Int64.Type}, {"Case Ref", Int64.Type}, {"Incident Ref", Int64.Type}, {"Item 1", type text}, {"Quantity 1", Int64.Type}, {"Item 2", type text}, {"Quantity 2", Int64.Type}, {"Item 3", type text}, {"Quantity 3", Int64.Type}, {"Item 4", type text}, {"Quantity 4", Int64.Type}, {"Item 5", type text}, {"Quantity 5", Int64.Type}, {"Payment Ref", type text}, {"Job Ref Unique", Int64.Type}, {"Type", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Quantity 5", "Item 5", "Quantity 4", "Item 4", "Quantity 3", "Item 3", "Quantity 2", "Item 2", "Quantity 1", "Item 1"}, "Attribute", "Value"),
//Add an Index column which starts from 0
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Only Selected Columns", "Index", 0, 1, Int64.Type),
//Add a custom column which gets the value of the next row in Value column. If there is no next row, return null
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index" [Value] {[Index] + 1} otherwise null),
//Split Attribute column by space delimiter
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
//Filter rows to remain rows with Item value in Attribute.1 column
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.1] = "Item")),
//Remove unwanted columns
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1", "Attribute.2", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Item"}, {"Custom", "Quantity"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Contract", "Job Ref", "Model", "Contractor", "Acc No", "Case Ref", "Incident Ref", "Item", "Quantity", "Payment Ref", "Job Ref Unique", "Type"})
in
#"Reordered Columns"
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi, thanks for the link but that is a little beyond my skill set at this stage.
I have tried various ways of grouping, transposing and unpivoting put can't get it right.
If you share some example data as a table to enable copy/paste or a link to a pbix or Excel file with mock data, I (or someone in the community) can provide M code for that specific transformation (using the ribbon buttons and/or an approach like in that video).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi, appreciate any help I can get with this, original table on first sheet and what I am trying to acomplish on the second sheet. https://easyupload.io/xhvalk
This is as far as I have got so far.
let
Source = Excel.Workbook(File.Contents("C:\Users\User\OneDrive\Documents\Book.xlsx"), null, true),
Table5_Table = Source{[Item="Table5",Kind="Table"]}[Data],
#"Unpivoted Only Selected Columns" = Table.Unpivot(Table5_Table, {"Item 1", "Quantity 1", "Item 2", "Quantity 2", "Item 3", "Quantity 3", "Item 4", "Quantity 4", "Item 5", "Quantity 5"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Text.Length([Attribute]) = 6 then "Item"
else "Quantity"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Contract", "Job Ref", "Model", "Contractor", "Acc No", "Case Ref", "Incident Ref", "Payment Ref", "Job Ref Unique", "Type", "Custom", "Value"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}})
in
#"Changed Type"
Thanks
Hi @Trebor84
You could try below codes. I have added some comments in it to make it clearer.
let
Source = Excel.Workbook(File.Contents("D:\TestData\Book - Copy.xlsx"), null, true),
Table5_Table = Source{[Item="Table5",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table5_Table,{{"Date", type date}, {"Contract", type text}, {"Job Ref", Int64.Type}, {"Model", type text}, {"Contractor", type text}, {"Acc No", Int64.Type}, {"Case Ref", Int64.Type}, {"Incident Ref", Int64.Type}, {"Item 1", type text}, {"Quantity 1", Int64.Type}, {"Item 2", type text}, {"Quantity 2", Int64.Type}, {"Item 3", type text}, {"Quantity 3", Int64.Type}, {"Item 4", type text}, {"Quantity 4", Int64.Type}, {"Item 5", type text}, {"Quantity 5", Int64.Type}, {"Payment Ref", type text}, {"Job Ref Unique", Int64.Type}, {"Type", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Quantity 5", "Item 5", "Quantity 4", "Item 4", "Quantity 3", "Item 3", "Quantity 2", "Item 2", "Quantity 1", "Item 1"}, "Attribute", "Value"),
//Add an Index column which starts from 0
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Only Selected Columns", "Index", 0, 1, Int64.Type),
//Add a custom column which gets the value of the next row in Value column. If there is no next row, return null
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index" [Value] {[Index] + 1} otherwise null),
//Split Attribute column by space delimiter
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
//Filter rows to remain rows with Item value in Attribute.1 column
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.1] = "Item")),
//Remove unwanted columns
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1", "Attribute.2", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Item"}, {"Custom", "Quantity"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Contract", "Job Ref", "Model", "Contractor", "Acc No", "Case Ref", "Incident Ref", "Item", "Quantity", "Payment Ref", "Job Ref Unique", "Type"})
in
#"Reordered Columns"
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
This video walks through one way to accomplish this type of transformation.
Faster Data Transformations with List/Record M Functions - YouTube
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |