Forum Discussion
Pivot table from 2 sets of data
Hi Joacols
I'm glad to hear that you consider already using Power Pivot 🙂
And you are doing also already the right thing by creating a bridge table with unique item codes, because that's what you would need for building relationships between the two tables.
If you have automated the creation of the bridge table (e.g. with UNIQUE function in M365 or with Power Query), from my point of view, there is not a lot that you could do better.
Hi Martin_Weiss, thank you very much for your reply.
Would you mind telling me how to automate the bridge table? I have never used the tools you mentioned.
Also, in the set of data, there where a few items without an item code (it is blank), so, when creating the bridge table and later linking it with the other tables, I wasn't able to get those items in blank (and I need to show them on my pivot table). Would you know how to solve this?
Hi Joacols
to automate the creation of a bridge table, you would need to load the in-table and out-table into Power Query. There you would remove all columns from both tables appart from the item-code column and then append both tables. Last transformation step is to remove duplicates in the item-code.
Then you have your bridge table containing all item-codes from in- and out-table.
As variant you may build virtual relationship. As an example, for such model
measures are
Total In:=SUM( 'In'[In] ) Total Out:=CALCULATE( SUM( Out[Out] ), TREATAS( VALUES('In'[Code]), Out[Code] ) )Virtual relationship affects performance, but that's on relatively large datasets. Within Excel that usually not an issue.
