Vlookup functionality error in Power Pivot

[soshaughnessy]

Hello All,

I am new to Power Pivot and I am exploring whether it would be possible to migrate some of my data analytic tools into this platform,however I am running into what may be a deal killer. Any insights would be greatly appreciated.

I have several worksheets that I previously would use a vlookup to bring in certain fields based upon the unique identifier, Record Number. The many to many doesn't apply here as it simply returns the first value it finds based on the identifier. As these are financial data sets there are always a many to many relationship but the unique identifier allows me to compare. I've setup a simple example that hopefully someone can help with.

I have two tables: PCO Report and Previous Report. I am trying to return the previous status of each record based off its record number into the PCO Report Table from the Previous Report Table. To do this I setup another table that only has the unique values with duplicates removed and built the relationships, to Record Number.

I have tried using Related and Related Table and neither will work. I continue to get the error "The column 'Previous Report[Status] either doesn't exist or doesn't have a relationship to any table available in the current context."

I am blocked from filesharing so apologies for no screenshot below is essentially what the tables look like

PCO Report Table

Project Name	Record Number	Status	PCO Number	Cost	Previous Status
Test 1	1	Approved	1F	50	Error
Test 1	1	Approved	1F	25	Error

<tbody>
</tbody>

Record Number Table

Record Number

1

<tbody>
</tbody>

Previous Report Table

Project Name	Record Number	Status	PCO Number	Cost
Test 1	1	Submitted	1F	50
Test 1	1	Submitted	1F	25

<tbody>
</tbody>

Is this at all a possibility? Lookupvalue at least acted as if it could understand the relationship but it didn't work as I got the error 'PCO Report'[]: A table of multiple values was supplied where a single value was expected. Is there a way to simply say return the first value you find that matches?

 

[Matt Allington]

I have no doubt there is nothing that is a deal breaker - but you need to think differently than the Excel world. What is the difference between the data in PCO Report and Previous Report? What is the business context of the relationship between these tables?

 

[soshaughnessy]

I hope you're right Matt!

In this specific instance they are the same data set taken at 2 different times. I am attempting to bring the status from the previous report into the new report to see if it has changed. IE has this record gone from submitted to approved status. The problem however is that the record spans several rows in each data set resulting in a many to many relationship.

This is just an example, there will be many instances in which I will need to do this. For instance I have budgets in one sheet and invoices in another sheet and I will need to bring the invoice data to the budget sheet based on the unique record number ID.

 

[Matt Allington]

It was my hunch that these were the same table, different times. Do you have a date column, or some other column to identify which came first? If not, all is not lost. I would load all the data from both tables into a single table in Power Pivot. You can use Power Query to do that and I wrote a blog about it here Consolidated Worksheets with Power Query - PowerPivotPro

If there is not a column identifying which came first (eg a month column) , then add one using Power Query or Excel. Add a calendar table to the data model with the same month column and an ID column. Read about that (particularly the ID column) in my Knowledge Base here Power Pivot Calendar Tables - Excelerator BI Join the tables using the common column (that's a mouthful - common column!)

Then you can write dax measures to compare the latest value with the previous value. Don't use a calculated column - read about that here Calculated Columns vs Measures in DAX - Excelerator BI

The measures could be something like this.

PCO Status:=if(hasonevalue(PCOTable[Status]),values(PCO[Status]))

Previous PCO Status:=calculate([PCO Status], Filter(all(calendar),calendar[ID] = max(calendar[ID])-1))

This second formula assumes you have a record for each project each month. If that is not the case (ie it could be 3 months ago when you had the last status, you will need a different formula.

 

[soshaughnessy]

Wow Matt I read through everything quickly and that looks great! I'm not at my computer but I'll start working with this ASAP! I haven't worked with power query yet but I'm intrigued. Thank you very much! I'm going to check out your book as well. I'm really hoping that these are the resources I need to taken my reporting to the next level rather than relying on manually updated data dumps with massive formulas running down every worksheet!

I'll keep you posted!

 

[Matt Allington]

If manually updated data dumps are your problem, then definitely power pivot and power query are your best new friends.