How to create a master table from an existing table

[o59393]

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:

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!

[Greg_Deckler]

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

[o59393]

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!

[Greg_Deckler]

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

[o59393]

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.

[Greg_Deckler · 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.

[o59393]

Thanks 😄  @Greg_Deckler 

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

It worked.

Appreaciate your help!

[Greg_Deckler]

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.