Forum Discussion
Reference a dimension table column in PQE
I have a Sales (fact) table with Products and Order Dates. I have a Products (dimension) table with PrepDays. There is a many-to-one relationship between them. I want to use M Language to create...
Hi Anonymous ,
In your fact table, try using this code in a new custom column:
Table.SelectRows(Products, (dim) => dim[Product] = [Product])[PrepDays]{0}This will bring in the [PrepDays] value from the dim table against each matching value of Product in the fact table.
Depending on the size of the Products table, you may find that PQ has to scan the table many times to find matches. In this case, you can buffer the Products table to hold it in memory while scanning, avoiding the need for multiple loads:
Table.SelectRows(Table.Buffer(Products), (dim) => dim[Product] = [Product])[PrepDays]{0}Pete
Agreed, but OP specifically wants an 'educational' solution:
Pete
- I think both yours and mine are educational in that both are done in power query. Neither is done in DAX. I meant for him to use his power query add column code after merging the tables in power query
Ah, I see. When they mentioned 'merging' the dimension onto the fact I assumed in PQ as I think it's a 'join' in DAX, but you could well be right.
Pete