Table Combination w More Fields in Power Query

Power Query
1

Dear Forum,

I am trying to solve the following task and would like to ask for your help.

I have 2 tables:

-one with Product Revenue Forecast per Q
-one with Regional Split per Q:

image

I would like to combine them in order to obtain a table with the Revenue Forecast by Product per Q and Region based on the provided split

image

Thanks a lot in advance for your precious help

Have a nice day

Antonio

Tables Combination eDNA Forum.xlsx (15.5 KB)

1 Like
2

Here’s the method, but the calcs are slightly out as I pasted your data into the PBIX so I lost some of the decimal points in the region table. If you follow my process but pull the data direct from your excel tables then you’ll get the right figures as you won’t lose the numbers beyond 2 decimal places.

image
image1089×250 27.9 KB

Select the Prod column, go to Transform and select Unpivot Columns

Which will give you this

image

Repeat this process in the other query that contains Region.

I then went to Merge Queries - Merge Queries as New, merged the queries on the Quarter column and tidied it up a bit.

PBIX for Antonio.pbix (29.0 KB)

2 Likes
3

Solution after unpivot and merge:
Solution.pbix (25.1 KB)

Hope it helps.

1 Like
4

Thanks a lot @DavieJoe, it works perfectly

Have a nice evening!

1 Like
5

Thanks a lot @mspanic for the help, it works!

Have a nice evening

6

No probs Antonio, thanks for posting this, made me realise that I lost the decimals when I pasted the data into a table. A helpful learning experience all round.

Have a great evening.

David

7

No worries at all, they were just example tables, I mainly needed to understand the logic and apply it to the real dataset :wink: Thanks!

2 Likes