M query add new rows based on a condition

[md8422]

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!

[BA_Pete · Accepted solution]

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]

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]

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