Forum Discussion
Post ProdigyHow 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:
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!
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.
7 Replies
- Greg_Deckler
Community Champion
In DAX, use SUMMARIZE or GROUPBY. In Power Query, use Group By.- 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
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
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_DecklerOther than you shouldn't need the DISTINCT I do not believe but I could be mistaken.
