check data while importing

[Anonymous]

Good day,

I am new bee and trying to create a logic so that before importing the new data in PowerBi, it checks the 2 fields (IPAddress+ServerName) from the new file into existing file.

For e.g. If data (2 fields are not matched or no duplicates) doent exist, it would continue to append that record from new file to old file and also update status column field to "NEW" to mark this record as a new record.

If data (2 fields are matched and duplicate record exist) exist than it no record added, just  update status column field to "ACTIVE" for that row.

What is the best approach to manage that ? Just looking for the pointers in the right directions. Thank you

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

Table3:

You may create a function with the following m codes in 'Advanced Editor'.

let
    Merge1 = (t1 as table,t2 as table) =>
let
    Source1 = Table.NestedJoin(t1, {"IPAddress", "ServerName"}, t2, {"IPAddress", "ServerName"}, "T", JoinKind.LeftOuter),
    Source2 = Table.NestedJoin(t2, {"IPAddress", "ServerName"}, t1, {"IPAddress", "ServerName"}, "T", JoinKind.LeftAnti),
    Source3 = Table.NestedJoin(t1, {"IPAddress", "ServerName"}, t2, {"IPAddress", "ServerName"}, "T", JoinKind.Inner),
    Result = Table.RemoveColumns( Table.Combine({Source1,Source2}),"T"),
    Res = Table.AddColumn(
        Result,
        "Status",
        each if 
        Table.RowCount(
            Table.SelectRows(
                Source3,
                (x)=>x[IPAddress]=[IPAddress] and x[ServerName]=[ServerName]
            )
        )>0
        then "Active"
        else "New"
    )
in
    Res
in
    Merge1

Append 'Table1' and 'Table2':

Append 'Table2' and 'Table3':

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

That is standard functionality in Power Query.  Describe your rules (as you did above) and then implement them in Power Query.   Ideally your files sit in the same folder structure so you can use the Folder connector rather than hard coding individual files as sources.