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 which attribute are present for a specific object and their values.

Each object is part of a Class

 

The other table "Requirements" store all the attributes required for a specific object class

 

I want to create a table in M query where for each object_id, I'll add new rows in Records with the missing attributes required for that specific class.

 

Here the example:

 

Any idea? 

 

 

THanks!

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

3 Replies

  • 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