Maintain unique row identifier after unpivot in Power Query

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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.:eeek:

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!
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top