Forum Discussion

DRothwein's avatar
DRothwein
Copper Contributor
Feb 26, 2021

Consolidate duplicates in power query

I have successfully combined two different sources of data that have multiple duplicates.  When I remove duplicates I lose the data from the secondary sheet that I used in the query.  I want to conso...
BI & Data Analysis
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 27, 2021

DRothwein 

Is it possible to illustrate on small sample file?

It's not clear under "combined two different sources" you mean appending or merging. If merging, which one?

"Duplicates" means exactly the same values in all columns or in few or only ID:s?

Depends on that what do you mean under consolidation, which shall be the result?

 

  • DRothwein's avatar
    DRothwein
    Copper Contributor
    Mar 01, 2021

    Here is an example where i have appended together the two different sheets. The first two columns have 97 duplicates. You can see where the information from the first spread sheet is in the first few columns and then the second spread sheet information is in the duplicate row. I would like the second spread sheet information to be in the same row and consolidate the data but its not letting me do this...






    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 01, 2021

      DRothwein 

      I'm not sure why you append, not merge. Let take such sample

      Desired result is

      We could receive it by merging one cell with another and expanding resulting column with the table. Generated script is

      let
    Source = Table.NestedJoin(Table1, {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Three", "Four"}, {"Three", "Four"})
in
    #"Expanded Table2"

      If append replace blank values on null (not sure why you don't have it by default), sort by ID, Fill Up and Fill Down proper columns, select all and remove duplicates. This way is not very reliable, depends on your concrete data. Script could be like

      let
    Source = Table.Combine({Table1, Table2}),
    #"Sorted Rows" = Table.Sort(Source,{{"ID", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"One", "Two"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Three", "Four"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Up")
in
    #"Removed Duplicates"

Resources