Forum Discussion
Solved
Pivot sales table
I have a data table like this:
| Product | Date (month 1) | Month 1 Rev. | Date (month 2) | Month 2 Rev. | Date (month 3) | Month 3 Rev. |
| Apple | 1/1/2022 | 4658 | 2/1/2022 | 599 | 3/1/2022 | 6546 |
| Orange | 1/2/2022 | 1646 | 2/2/2022 | 5646 | 3/2/2022 | 254 |
| Kiwi | 1/3/2022 | 5494 | 2/3/2022 | 6546 | 3/3/2022 | 545 |
I want to transform it like this:
| Date | Product | Rev |
| 1/1/2022 | Apple | 4658 |
| 1/2/2022 | Orange | 1646 |
| 1/3/2022 | Kiwi | 5494 |
| 2/1/2022 | Apple | 599 |
| 2/2/2022 | Orange | 5646 |
| 2/3/2022 | Kiwi | 6546 |
| 3/1/2022 | Apple | 6546 |
| 3/2/2022 | Orange | 254 |
| 3/3/2022 | Kiwi | 545 |
Is it possible to achieve this by pivot/unpivot? I have tried to create the secnd query for each month and then append 12 queries into one query. It works but I am wonderiing if there is more effective way to achieve this. Appreciate your insight!
Hi,
Try following M code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEy1DfUNzIwMgIyTcxMLYCUEULE1NISSBojBMxMTcyUYnWilfyLEvPSIfqNYJKGZkBJkH64iClExBghYmRqAtbvnVmeCdZtDFdrYmkC1m2MYhlIN5IaU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Date (month 1)" = _t, #"Month 1 Rev." = _t, #"Date (month 2)" = _t, #"Month 2 Rev." = _t, #"Date (month 3)" = _t, #"Month 3 Rev." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date (month 1)", type date}, {"Month 1 Rev.", Int64.Type}, {"Date (month 2)", type date}, {"Month 2 Rev.", Int64.Type}, {"Date (month 3)", type date}, {"Month 3 Rev.", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Value"}, {"Added Index1.Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each Number.IsOdd([Index.1])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Date"}, {"Added Index1.Value", "Product#(tab)Rev"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Product", "Date", "Product#(tab)Rev"})
in
#"Removed Other Columns"Thank you.
4 Replies
Hi,
Try following M code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEy1DfUNzIwMgIyTcxMLYCUEULE1NISSBojBMxMTcyUYnWilfyLEvPSIfqNYJKGZkBJkH64iClExBghYmRqAtbvnVmeCdZtDFdrYmkC1m2MYhlIN5IaU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Date (month 1)" = _t, #"Month 1 Rev." = _t, #"Date (month 2)" = _t, #"Month 2 Rev." = _t, #"Date (month 3)" = _t, #"Month 3 Rev." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date (month 1)", type date}, {"Month 1 Rev.", Int64.Type}, {"Date (month 2)", type date}, {"Month 2 Rev.", Int64.Type}, {"Date (month 3)", type date}, {"Month 3 Rev.", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Value"}, {"Added Index1.Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each Number.IsOdd([Index.1])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Date"}, {"Added Index1.Value", "Product#(tab)Rev"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Product", "Date", "Product#(tab)Rev"})
in
#"Removed Other Columns"Thank you.
Hi Dinesh_Suranga , I am new to PBI and have no code skill. Would you explain the code step by step and mark out where should I edit with my own source/table/field name?
Dinesh_Suranga I made it! please ignor my previous post. I have figured it out by following the transformation steps you wrote. Thank you so much!
Thank you so much! Will try the code next week and let you know.
