Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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 , 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/
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |