cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors