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
o59393
Post Prodigy
Post Prodigy

How to create a master table from an existing table

Hi all

 

I need to create a "Master" table that contains the catalog of the unique products and its respective brand, category and segment.

 

The columns are stored in a table called Query1 with a lot of duplicates for all these columns:

 

query111.PNG

 

So far I could get only the Bev Product column with this dax:

 

Beverage Product Table = 

  DISTINCT(


      SELECTCOLUMNS('Query1',"Beverage Product",Query1[[L1.3 - Bev Product]]])
   
)

 

 

The expected outcome I want is this:

 

https://1drv.ms/x/s!ApgeWwGTKtFdhkwiJCQdCuXi-bp0?e=jkMLfT

 

Thanks!

1 ACCEPTED SOLUTION

Master Table = 

SUMMARIZE(
  'Query1',
  'Query1'[L1.3 - Bev Product]], 
  'Query1'[L1.4 - Brand]], 
  'Query1'[L1.7 - Bev Category], 
  'Query1'[L1.8 - Bev Segment]
)

Unless your column names actually have square brackets in their names, then you would need your double square brackets.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

In DAX, use SUMMARIZE or GROUPBY. In Power Query, use Group By.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

Like this?

 

Master Table = 
DISTINCT(
SUMMARIZE( Query1, Query1[[L1.3 - Bev Product]]],Query1[[L1.4 - Brand]]],Query1[[L1.7 - Bev Category]]],Query1[[L1.8 - Bev Segment]]])
)

 

Thanks!

Other than you shouldn't need the DISTINCT I do not believe but I could be mistaken.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

I want it to be on dax, since it will be a brand new table.

 

What should be the order?

 

Master Table = 

SUMMARIZE(
(Query1,
  DISTINCT(
    SELECTCOLUMNS('Query1',"Beverage Product",Query1[[L1.3 - Bev Product]]]), 
    SELECTCOLUMNS('Query1',"Brand",Query1[[L1.4 - Brand]]], 
    SELECTCOLUMNS('Query1',"Beverage Category",Query1[[L1.7 - Bev Category]]], 
    SELECTCOLUMNS('Query1',"Beverage Segment",Query1[[L1.8 - Bev Segment]]], 
)

 

 

I did that but it's not reading it.

 

Thanks.

Master Table = 

SUMMARIZE(
  'Query1',
  'Query1'[L1.3 - Bev Product]], 
  'Query1'[L1.4 - Brand]], 
  'Query1'[L1.7 - Bev Category], 
  'Query1'[L1.8 - Bev Segment]
)

Unless your column names actually have square brackets in their names, then you would need your double square brackets.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks 😄  @Greg_Deckler 

 

I used the distinct to avoid duplicates in the beverage product name.


It worked.

 

Appreaciate your help!

Great! The reason I didn't think DISTINCT should be necessary is that SUMMARIZE should take care of that by itself specifying the Product name as a group by column.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.