help with power query builder

Anonymous , the first 2 rows in table 1 are exactly the same. The same is true with table 2. If you join without a unique key data will get duplicated

You can do like:https://radacad.com/append-vs-merge-in-power-bi-and-power-query

If you do not have unique key columns, then you can create common dimensions and analyze them together 

Hi Anonymous ,

Basically, need to create a dimension like an index column to delete the same rows in one of the tables. I have created an index column in table1 and use the below M code to filter rows, merge the new table1 with table2 to get the expected result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQp2CYeQCpl5KZmJQLaRgZGBvqG+oVKsDlFKjFGUpOamIqkwooIKY7AKI7iK4pRyhcTS4pKixBxkx5gQp8xUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, App = _t, CI = _t, #"close Incident" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"App", type text}, {"CI", type text}, {"close Incident", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Custom1 = Table.SelectRows(#"Added Index",each 
            let
                    id=[ID],app=[App],ci=[CI],dc=[close Incident],
                    x = 
                    Table.AddColumn(
                        #"Added Index","new",each
                        Table.RowCount(
                                    Table.SelectRows(
                                        #"Added Index",each
                                        [ID]=id and [App]=app and [CI]=ci and [close Incident]=dc
                                    )
                        )
                    )

                    
            in 
            (
            [Index]=
            Table.Max(
                        Table.SelectRows(
                            x,each
                            [ID]=id and [App]=app and [CI]=ci and [close Incident]=dc
                        ),
            "Index")[ID]
            and
            Table.Max(
                        Table.SelectRows(
                            x,each
                            [ID]=id and [App]=app and [CI]=ci and [close Incident]=dc
                        ),
            "new")[new]>1
            )
            or
            (
            Table.Max(
                        Table.SelectRows(
                            x,each
                            [ID]=id and [App]=app and [CI]=ci and [close Incident]=dc
                        ),
            "new")[new]=1

            )
    ),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Index"})
in
    #"Removed Columns"

Table1 will be like this:

Merge table2 with it, expand columns and remove unnecessary columns, final result will be like this:

The details about M code that you can refer the Applied steps in the right of power query.

Sample file is attached that hopes to help you: help with power query builder.pbix

Best Regards,
Yingjie Li

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