Transpose raw data into multi-line calendar format in PowerQuery

[wunderfitz]

Hi,

I've scoured the web and YouTube, and often found solutions that were quite close to what I'm trying to do, but not close enough. Maybe someone here has an idea...

I'm trying to use PowerQuery to compile various data sources into a single table with raw data for customer work orders for appr. 10000 customers.
I then want to transpose it into an Excel Table (as opposed to an Excel range) in a calendar view, so that
- a customer can have 0, 1 or multiple contracts
- a contract can have 1 or more work orders
- work orders can span multiple months or multiple work orders can occur in one month (i.e. weekly or biweekly work orders)

The raw data initially looks something like this:

Customer 01	Contract 01-a	start: jan 1st 2022 / end: jan 31st 2022	Work Order 01-a-01
Customer 01	Contract 01-a	start: feb 1st 2022 / end: feb 28th 2022	Work Order 01-a-02
Customer 01	Contract 01-a	start: mar 1st 2022 / end: mar 31st 2022	Work Order 01-a-03
Customer 01	Contract 01-b	start: apr 1st 2022 / end: jun 31st 2022	Work Order 01-b-04
Customer 02	Contract 02-a	start: jan 1st 2022 / end: jan 31st 2022	Work Order 02-a-01 (1 of 2)
Customer 02	Contract 02-a	start: jan 1st 2022 / end: jan 31st 2022	Work Order 02-a-02 (2 of 2)
Customer 02	Contract 02-a	start: feb 1st 2022 / end: feb 28th 2022	Work Order 02-a-03 (1 of 2)
Customer 02	Contract 02-a	start: feb 1st 2022 / end: feb 28th 2022	Work Order 02-a-04 (2 of 2)
Customer 02	Contract 02-a	start: mar 1st 2022 / end: mar 31st 2022	Work Order 02-a-05 (1 of 2)
Customer 02	Contract 02-a	start: mar 1st 2022 / end: mar 31st 2022	Work Order 02-a-06 (2 of 2)
Customer 03	no contract	--	--
...	...	...	...

and I'd like to display it like this:

Customer	Contract	Jan	Feb	Mar	Apr	May	Jun	...
Customer 01	Contract a	Work Order 01-a-01	Work Order 01-a-02	Work Order 01-a-03				...
Customer 01	Contract b				Work Order 01-b-04	Work Order 01-b-04	Work Order 01-b-04	...
Customer 02	Contract a	Work Order 02-a-01	Work Order 02-a-03	Work Order 02-a-05				...
Customer 02	Contract a	Work Order 02-a-02	Work Order 02-a-04	Work Order 02-a-06				...
Customer 03	no contract	--	--	--	--	--	--	...
...	...	...	...	...	...	...	...	...

I'm actually quite close, in that I can generate one line of data for every customer-contract-workorder-month combination, I can even transpose and group the data, so that any customer-contract combination is on a separate line, but I fail miserably when it comes to ending up with n lines when there are n work orders for each month, where n can be between 1 and 9.

I have already tried "grouping" in PowerQuery, using the option to not aggregate, but that will result in a bunch of subtables which I am not sure how to fit into the calendar.

I also do not want the results to be staggered, i.e.

Customer		Jan	Feb
Customer 01	Contract a	WO 1
Customer 01	Contract a	WO n
Customer 01	Contract a		WO n+1
Customer 01	Contract a		WO n+n

but would instead prefer

Customer		Jan	Feb
Customer 01	Contract a	WO 1	WO n+1
Customer 01	Contract a	WO n	WO n+n
Customer 01	Contract a		WO 03
Customer 01	Contract a		WO 04

 

[wunderfitz]

... ignore the last 2 lines of that table - the editor would not let me...

i.e. I want to have achieve a calendar that is as compact as possible without aggregating the detail data, but instead distributing it over all 12 months and - if necessary - over multiple lines.