Data --> Pivot Table

[therealjdj]

Ok team -- I got a question to make Data useable in a Pivot Table.

So let's say I get a report that looks like the top table in the attached image. It has a breakdown of customers, what they purchased, when we booked the order, and when we invoiced the order. From what I can tell this data is not usable in a Pivot Table.
It would have to be modified to look more like the second table. Where each individual order has a line for each transaction.
The end is the sort of pivot table I would like to have.

Is this correct?

 

[alansidman]

Cannot manipulate data in a picture. Can you reload using XL2BB so that we don't have to retype all your data to help you.

 

[therealjdj]

Cannot manipulate data in a picture. Can you reload using XL2BB so that we don't have to retype all your data to help you.

Here you go:

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				Bookings						Sales
2				2022			2023			2022			2023
3		Customer	Product	Oct	Nov	Dec	Jan	Feb	March	Oct	Nov	Dec	Jan	Feb	March
4		1	A	$ 10.00								$ 5.00	$ 5.00
5		1	C				$ 4.00						$ 3.00	$ 1.00
6		1	G		$ 2.00							$ 1.00			$ 1.00
7		2	G			$ 8.00							$ 1.00	$ 6.00	$ 1.00
8		2	A					$ 4.00							$ 4.00
9		3	B		$ 20.00							$ 5.00	$ 5.00	$ 5.00	$ 5.00
10		4	A	$ 10.00										$ 10.00
11		4	G				$ 4.00								$ 4.00
12
13
14		Customer	Product	Year	Month	Booking	Sales
15		1	A	2022	Oct	$ 10.00
16		1	A	2022	Dec		$ 5.00
17		1	A	2023	Jan		$ 5.00
18		1	C	2023	Jan	$ 4.00
19		1	C	2023	Jan		$ 3.00
20		1	C	2023	Feb		$ 1.00
21		1	G	2022	Nov	$ 2.00
22		1	G	2022	Dec		$ 1.00
23		1	G	2023	March		$ 1.00
24
test3

 

[alansidman]

A Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Down", {{"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Merged", "Column3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"::Customer", "::Product"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"::Customer", Order.Ascending}, {"::Product", Order.Ascending}, {"Attribute.2", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Sales", each if Text.StartsWith([Attribute.1],"Sales") then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Bookings", each if Text.StartsWith([Attribute.1],"Book") then [Value] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute.1", "Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"::Customer", "::Product", "Attribute.2", "Attribute.3", "Bookings", "Sales"})
in
    #"Reordered Columns"

::Customer	::Product	Attribute.2	Attribute.3	Bookings	Sales
1	A	2022	Dec		5
1	A	2022	Oct	10
1	A	2023	Jan		5
1	C	2023	Feb		1
1	C	2023	Jan		3
1	C	2023	Jan	4
1	G	2022	Dec		1
1	G	2022	Nov	2
1	G	2023	March		1
2	A	2023	Feb	4
2	A	2023	March		4
2	G	2022	Dec	8
2	G	2023	March		1
2	G	2023	Jan		1
2	G	2023	Feb		6
3	B	2022	Dec		5
3	B	2022	Nov	20
3	B	2023	March		5
3	B	2023	Feb		5
3	B	2023	Jan		5
4	A	2022	Oct	10
4	A	2023	Feb		10
4	G	2023	March		4
4	G	2023	Jan	4

 

[therealjdj]

Thank you!
That code worked great on this example. Now I can hopefully take what you showed me and expand it to my larger problem that I am trying to solve.

Thanks again! I have never yused Power Query but am looking forward to learning the tool