Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lwklwk98
Helper I
Helper I

Applying a Condition on creating a new table from existing table

Hello PowerBI Community,

 

I have a mock dataset: (Called TableA)

 

Company NameProduct Type (Only 2 Types)1Q20 Qty1Q20 Profit2Q20 Qty2Q20 Profit....4Q22 Qty4Q22 Profit
...A.....................
...B.....................
...B.....................
...A.....................

 

I would like to create a table as shown below: 

 

PeriodQtyProfit
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!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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/

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.