Applying a Condition on creating a new table from existing table

[lwklwk98]

Hello PowerBI Community,

I have a mock dataset: (Called TableA)

Company Name	Product Type (Only 2 Types)	1Q20 Qty	1Q20 Profit	2Q20 Qty	2Q20 Profit	....	4Q22 Qty	4Q22 Profit
...	A	...	...	...	...	...	...	...
...	B	...	...	...	...	...	...	...
...	B	...	...	...	...	...	...	...
...	A	...	...	...	...	...	...	...

I would like to create a table as shown below: 

Period	Qty	Profit
2020-01	...	...
2020-04	...	...
...	...	...

This is the code I currently have for creating the table here:

NewTable = 

UNION ( ROW ( "Period", "2020-01", "Qty" , SUM (TableA[1Q20 Qty]), "Profit" , SUM (TableA[1Q20 Profit]) ),

               ROW ( ....
)

I have 12 of such rows above to cover all 3 years. The problem I encounter would be that I need to separate the table according to the 2 different product types (A & B). The best is this can be displayed in 1 table but creating 2 different tables is fine as well.

Do advice if there is a simpler method to create the table. 

Thank you very much!

[amitchandak · Accepted solution]

@lwklwk98 , In DAX you will have code like this. One table with product type is a better option

In power query it is not going to be easy too

multiple unpivot you have to check

https://kohera.be/blog/power-bi/how-to-unpivot-twice/

[lwklwk98]

I un-pivoted my data as you mentioned and I realized that it might be better to do that instead.

Even though the cleaning process of duplicating the original table and removing unnecessary columns can be tedious, it allows me to leave a proper trace of my methods in the PowerQuery instead of a long line of hard codes. 

Thanks @amitchandak