Create a new table summarizing data from two sources in Power Query

Hi Anonymous 

Paste my query in Advanced Editor of a new query

query name: Source2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRMNJ3MVTSAbOAFJjtk5meUQKk9dxzkzJAtE9JilKsDli5ib6LMUS5CUg5iO2Xn5uZl5gDUhjsiKbc0EDfxQSi3tAApMEEVQNIKdQia5BumDZThDZTmDaP1MSySiRNwY5BPiiazPVdjKCazEGajJD9AlIN9xNUhzHcM8Ywz2BaguRzUySfmyIrRgmoWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key source 2" = _t, #"ID code source 2" = _t, #"Domain source 2" = _t, #"Sampling level source 2" = _t, #"Leac company source 2" = _t, #"Impacted companies source 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key source 2", type text}, {"ID code source 2", type text}, {"Domain source 2", type text}, {"Sampling level source 2", type text}, {"Leac company source 2", type text}, {"Impacted companies source 2", type text}})
in
    #"Changed Type"

Query name :Source1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDNCoMwEIRfJdirqIkGBU+CtBQ8KT2JBxtDK6gVtfT1m6zxJ7SeZljYb2cnz41rjLAdY8MEJwR81nN2wiECJaEURMXcSqZKyLkexgm9GHsPvGPcKEwAETsmM0gKeAC44cxzlMoL1qW9P4WmvOOfslkQrh27M0IKeNihy26wpbh1FR/QWLZ9U3ePheCv3/j6N3Ixi9Lk9yh21uDY0ZLrkbPo8CoWJXoKAS16C4IoBFEtkGD3/UGRmG40qtH2jP8tFF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key Source 1" = _t, #"ID code source 1" = _t, #"Domain source 1" = _t, #"Specs source 1" = _t, #"Lead Lead company source 1" = _t, #"Qualification type source 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key Source 1", type text}, {"ID code source 1", type text}, {"Domain source 1", type text}, {"Specs source 1", type text}, {"Lead Lead company source 1", type text}, {"Qualification type source 1", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key Source 1"}, Source2, {"Key source 2"}, "Source2", JoinKind.FullOuter),
    #"Expanded Source2" = Table.ExpandTableColumn(#"Merged Queries", "Source2", {"Key source 2", "ID code source 2", "Domain source 2", "Sampling level source 2", "Leac company source 2", "Impacted companies source 2"}, {"Source2.Key source 2", "Source2.ID code source 2", "Source2.Domain source 2", "Source2.Sampling level source 2", "Source2.Leac company source 2", "Source2.Impacted companies source 2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Source2", "Key Master", each if [Key Source 1] = null then [Source2.Key source 2] else [Key Source 1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ID Master", each if [ID code source 1] = null then [Source2.ID code source 2] else [ID code source 1]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Domain Master", each if [Domain source 1] = null then [Source2.Domain source 2] else [Domain source 1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Key Source 1", "ID code source 1", "Domain source 1", "Source2.Key source 2", "Source2.ID code source 2", "Source2.Domain source 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Lead Lead company source 1", "Lead company 1"}, {"Source2.Leac company source 2", "Lead company 2"}, {"Qualification type source 1", "Qualification type"}, {"Source2.Impacted companies source 2", "Impacted companies"}, {"Source2.Sampling level source 2", "Sampling level"}, {"Specs source 1", "Specs"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Key Master", "ID Master", "Domain Master", "Lead company 1", "Lead company 2", "Impacted companies", "Qualification type", "Specs", "Sampling level"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Key Master", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.