Pivot table from 2 sets of data

Copper Contributor

Hi everyone,

I need to analyse 2 sets of raw data. One is related to inbound stock and the otherone to outbound. Each set of data is in a separate sheet of the workbook. The columns on each set of data are item code, item group, weight of item.

What I need is, by using this 2 stes of data, automatically calculate the items on hand (by substracting inbound - outbound), and show the information per item code and item group.

The solution I thought is to relate both sets of data and create a pivot table using power pivot, however it is a little complicated because the column I need to use as relation (item code) has duplicated values, and I cannot relate them if this happens.. so I created a "bridge table" with only the unique values, to use as a way to relate all the tables. 

I was wondering, is there any other simpler way of solving my need?

Thanks

6 Replies

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?

@Joacols 

As variant you may build virtual relationship. As an example, for such model

image.png

measures are

Total In:=SUM( 'In'[In] )

Total Out:=CALCULATE( SUM( Out[Out] ), TREATAS( VALUES('In'[Code]), Out[Code] ) )

Hi @Sergei Baklan 

a very cool solution, I never saw such a virtual relationship before!

 

 

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.

@Martin_Weiss 

Virtual relationship affects performance, but that's on relatively large datasets. Within Excel that usually not an issue.