Pivot sales table

[RL_171]

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!

[RL_171]

@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!

[RL_171]

Thank you so much! Will try the code next week and let you know.

[Dinesh_Suranga · Accepted solution]

@RL_171 

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.

[RL_171]

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?