Maintain unique row identifier after unpivot in Power Query

[macfuller]

I'm creating a 2018 budget spreadsheet against which I'll track invoices for burndown charts. My budget rows are created as

LineID Vendor etc etc Jan Feb Mar ... Dec Total
XY001 IBM $50 $50 $100 $50 $xxx
AM005 Whole Foods $25 $35 $45 $100 $xxx
etc

When I pull the data into the model I naturally unpivot on the calendar dates. When I receive invoices my unpvioted burndown measures can tell what my cumulative budget and invoiced amounts are. When my monthly Whole Foods invoices arrive I want to link them to the AM005 budget line for easy pivot table comparisons instead of the horrendous formulas I had in my 2017 budget version.

However, unpivoting duplicates the unique LineID against which I want to match my invoices so I can't create a relationship in PowerPivot. Without the relationship I can't put my budget and invoice burndown measures into the same pivot table and have a single set of slicers give me breakdowns by vendor, department, etc.

I suspect I have to extract all my budget key fields into a unique lookup table that links to both the budget and invoice tables, but I'm hoping there's a special trick I've missed where I could avoid that.

 

[Matt Allington]

I suspect I have to extract all my budget key fields into a unique lookup table that links to both the budget and invoice tables, but I'm hoping there's a special trick I've missed where I could avoid that.

Correct. You simply do what you are doing, but also create a new lookup table. You can do this in Power Query easily by referencing the source table, remove other column and and then remove duplicates.

 

[macfuller]

Yup. Done.

I have to send this to our budget team. Power Pivot and DAX are already like the 5th dimension (not the Motown group) to them, so adding tables and relationships causes no end of discussions.

Oh well. I look forward to the day when PP, PQ, and DAX are as common amongst Excel users as SUM() (let alone SUMPRODUCT()). I know you are working toward that day - keep fighting the good fight!