Transforming data into new structure

[Nilson]

Hello everybody,

now that I have failed several times, I want to ask for your support.

As a basis, I have shown several tables in the form of (1). I have a fixed number of 5 products at the moment with the corresponding numbers per month. The numbers per month are continuous, so they expand every month.

Now I want to translate the products from (1) into new product names by (2). Subsequently (3) is to be recorded, where all products from (4) are recorded and the monthly values ​​of the same product names are now added up (Prod_AA = Prod_01 + Prod_02).

I hope I was able to describe the problem to you to some extent. I would be really happy for your help.

Kind regards 🙂

[Adamtall · Accepted solution]

Hi,

You need the table (2)  and make a relationship to the "fact" table (1)

/Adam

[VasTg · Accepted solution]

@Nilson 

Do you want to create 3 as a visual or a table?

If it has to be a visual, follow @Adamtall approach as below.

Define relationships as below.

4->2 (Prod_ID_NEW)

2->1(Prod_ID,Prod_id_old)

Create a visual from 4(prod_id_new),1(Month1,2,3 etc ->Summarize by SUM).

If you want to create 3 as a table, I would recommend to merge 1,2,4 in power query merge and do a GROUP BY prod_id_new.

If it helps, mark it as a solution

Kudos are nice too

[Nilson]

I was thinking way too complex!!
The representation as a visual is completely sufficient for my application.

Thank you both so much! 👍

[Adamtall · Accepted solution]

Hi,

You need the table (2)  and make a relationship to the "fact" table (1)

/Adam