Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 months ago
Solved

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...
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    10 months ago

    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

BA_Pete's avatar
BA_Pete
Super User
10 months ago

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