help with power query builder

[Anonymous]

Hi,

this might be really stupid query but i am struggling to get the desired output.

I am trying to join two tables. and would like the below output in the resultant table. 

Table 1 (this table is result of joining two other tables)

ID	App	CI	date close Incident
1	SDW	SDW india	1/1/2020
1	SDW	SDW india	1/1/2020
3	SDW	SDW emea	1/2/2020
3	SDW	SDW emea	1/2/2020
3	SDW	SDW emea	1/3/2020
2	SDW	sdw australia	1/4/2020
2	SDW	sdw australia	1/5/2020

table 2  (this table is result of joining two other tables)

ID	App	CI	Problem close
1	SDW	SDW india	1/1/2020
1	SDW	SDW india	1/1/2020
3	SDW	SDW emea	1/2/2020
3	SDW	SDW emea	1/2/2020
3	SDW	SDW emea	1/3/2020
2	SDW	sdw australia	1/4/2020
2	SDW	sdw australia	1/5/2020
2	SDW	sdw australia	1/6/2020

result needed after joining table 1 and 2 above, basically i need to get the date column from 2nd table if the number of rows do not match it should be fileed with nulls. currently due to left join we are getting duplicates. instead of 8 records i get somewhere around 20-30 odd records. 

	Join
ID	App	CI	Problem close	date close Incident
1	SDW	SDW india	1/1/2020	1/1/2020
1	SDW	SDW india	1/1/2020	1/1/2020
3	SDW	SDW emea	1/2/2020	1/2/2020
3	SDW	SDW emea	1/2/2020	1/2/2020
3	SDW	SDW emea	1/3/2020	1/3/2020
2	SDW	sdw australia	1/4/2020	1/4/2020
2	SDW	sdw australia	1/5/2020	1/5/2020
2	SDW	sdw australia	1/6/2020	null

the CI column is what we have for common in the table. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[v-yingjl]

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.

[amitchandak]

@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 

[Anonymous]

Thank you i will try the method in the link.

And yes we do not have unique data in those two table. that is one of the issue. as mentioned table 1 and 2 are actually derived from 2 tables which had unique data.