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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.