Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Combining data sets with duplicate information (real tester!!)

Hi all!   I am trying to combine two data sets which contain duplicate information. Instead of getting my desired outcome of assigning a unique identifier, I am left with more duplicates.    Data...
Need Help
Smauro's avatar
Smauro
Solution Sage
5 years ago

Anonymous, try that:

Query name = ds1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWJVkqCs5KBrEowKwXOSgWyqsCsNDgrHc7KALLKwaxMICtRKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Role = _t]),
    #"Grouped Roles" = Table.Group(Source, {"Role"}, {{"Names", each [Name], type {text}}})
in
    #"Grouped Roles"

Query name = ds2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUdI1VIrVgTANwKwKIMsQzjICsyqBLGM4ywTMqgKyTOEsMzDLL79EIVGhKD8nVSEzTyGl2BAoY64UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Role = _t, #"Position ID" = _t]),
    #"Grouped Roles" = Table.Group(Source, {"Role"}, {{"Positions", each [Position ID], type {text}}})
in
    #"Grouped Roles"

Query name = anything you want

let
    #"Merge per Role" = Table.ExpandTableColumn(
        Table.NestedJoin(ds1, "Role", ds2, "Role", "ds2", JoinKind.LeftOuter),
        "ds2", {"Positions"}, {"Positions"}),
    
    #"Magically Combine Names and Positions" = Table.CombineColumns(#"Merge per Role",
        {"Names", "Positions"},
        (x) => List.Zip({x{0}??{}, x{1}??{} }),
        "Names & Positions"),
    #"Expanded Names & Positions" = Table.ExpandListColumn(#"Magically Combine Names and Positions", "Names & Positions")[#"Names & Positions"],
    #"Converted to Table" = Table.FromList(#"Expanded Names & Positions", (x) => x, {"Name", "Position ID"})
in
    #"Converted to Table"

 


Best,
Spyros