Forum Discussion

md8422's avatar
md8422
Frequent Visitor
4 years ago
Solved

M query add new rows based on a condition

Hi All,   I've been hitting a wall for the last few days on this problem, maybe somebody here has an idea on how to solve it.   I have 2 tables: Records and Requirements. The Records contains wh...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi md8422 ,

     

    Start with the Requirements table and crossjoin a distinct list of [object_id] by adding a custom column something like this:

    Table.Distinct(
    Table.SelectColumns(
        Records,
        {"object_id"}
    )
)

     

    Then merge this with your Records table on Requirements[attribute], [object_id] = Records[attribute], [object_id].

    Expand your merge column to add the [Value] field.

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi md8422 ,

 

Start with the Requirements table and crossjoin a distinct list of [object_id] by adding a custom column something like this:

Table.Distinct(
    Table.SelectColumns(
        Records,
        {"object_id"}
    )
)

 

Then merge this with your Records table on Requirements[attribute], [object_id] = Records[attribute], [object_id].

Expand your merge column to add the [Value] field.

 

Pete

md8422's avatar
md8422
Frequent Visitor
4 years ago

Thanks @BA_Pete for your answer... just one question: is there a better way than using crossjoin? .... expecially if these table contains +1000 rows each ?

 

Thanks again 

 

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi md8422 ,

     

    This is the quickest way I can think of to get your desired output.

    To try and do it the way you were thinking, i.e. match conditions and insert a row, would essentially require thousands of scans and insert row operations.

    Try my solution and let me know how you get on. I think the crossjoin performance will be better than you are expecting.

    You could also potentially speed it up by buffering the table to crossjoin:

    Table.Buffer(
    Table.Distinct(
        Table.SelectColumns(
            Records,
            {"object_id"}
        )
    )
)

     

    Pete