Find lines that have a value in a specific field that is not in the Master Data table.

[rpinxt]

Well I have done this before and now have to recreate it but I do not know anymore how I did it!

Strange this that the solution shows like a table but there is no logic.

So here is the table:

But as you can see in the formula bar there is nothing.

What this does is look in the main data for a field PF.

Then it compares it to a master data table in the model that also has field PF.

When in the new data it finds a value for PF that is not in the master data table it will list these lines in this CrossRefCheck table.

Only bit of logic I can find is in query editor:

So here in the query editor you see all tables involved.

AgeingDetails is the new data coming in, PF is the master data against which the new data is checked.

Then CrossRefCheck is the table that gets created by this little logic.

But how was it setup?? Cannot find a new table option in query editor.

[v-yiruan-msft · Accepted solution]

Hi @rpinxt ,

You can follow the steps below to get it:

1. Navigate to Power Query Editor,  Click New Source--> Blank Query to create a new query and rename this query as "CrossRefCheck"

2. Select the above query and click "Advanced Editor" in Home ribbon. Paste the codes in it just as the one in below screenshot

Best Regards

[rpinxt]

Well thanks @v-yiruan-msft but there is just the code that I already saw:

But how did I make this. Where did it start.

I was not done by addiing a new table:

So where do you put that source code in??

Source = Table.RemoveMatchingRows(AgingDetails,Table.ToRecords(PF),{"Prod.Fam."})

Where to create this table.removematchingrows logic ?

[v-yiruan-msft · Accepted solution]

Hi @rpinxt ,

You can follow the steps below to get it:

1. Navigate to Power Query Editor,  Click New Source--> Blank Query to create a new query and rename this query as "CrossRefCheck"

2. Select the above query and click "Advanced Editor" in Home ribbon. Paste the codes in it just as the one in below screenshot

Best Regards

[rpinxt · Accepted solution]

Yes thanks @v-yiruan-msft I also found the blank query.

Can do it like you did in advance editort.

But I just did it in the normal formula line:

Work also just fine.

Thanks.

[v-yiruan-msft]

Hi @rpinxt ,

You can go to Power Query Editor, select the query [CrossRefCheck]. The Advanced Editor lets you see the code that Power Query Editor is creating with each step. Please navigate to Home ribbion and click "Adavanced Editor" to open the page.

Best Regards