Reference a dimension table column in PQE

[WillBeeSEA]

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 a custom column in the Sales table to calculate the expected Shipping Date by adding the PrepDays to the Order Date.

(I could easily use the RELATED function and do it in DAX, or merge the column from the dimension table into the fact table, but I am trying to force myself to learn more about "M.")

I have been able to do it when the PrepTime column is in in the Sales table (where the Order Date is also):

 But when I try to reference the column (PrepDays) the Dimension table, it seems to be searching (the dot dash across the screen repeatedly) but then it times out in 30 seconds:

(I change the column name to avoid confusion between the queries.)

As in anything regarding computers, "Everything is simple but nothing is intuitive."

Thank you in advance for your help.

[ronrsnfld]

Another option would be to merge the two tables (Table.Join or Table.NestedJoin) using the :Product as the key. Then you could use your original formula on the merged table.

[BA_Pete]

Agreed, but OP specifically wants an 'educational' solution:

Pete

[ronrsnfld]

• 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

[BA_Pete]

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

[BA_Pete · Accepted solution]

Hi @WillBeeSEA ,

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

[WillBeeSEA]

Hi Pete,

Thank you for your input.  I used each of the strings you gave me, but the column still returns errors on each row.  Perhaps my reach has exceeded my grasp at this point in learning M.  I will go back to merging the dimension field into the fact table and using it that way.

Will