Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to remove duplicates from 2 columns and maintain unique value in 3rd column power query

I want to keep 1 row when there are  duplicates values in 3 separate columns for eg: 

 

I have this table

 

 

 

and I want the result to look like the one shown below

 

I have tried to remove use REMOVE DUPLICATES (pressing the CTRL Key on selecting all 3 columns but this doe snot work) Tried to use the GROUP BY feature but that does not allow me to select the resulting column dont know why, is there a way in Power query through which you can keep just keep 1 row and remove the other rows where there is a match 

Show and Tell
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago
    let
    Source = Odbc.Query("dsn=Foundation", "SELECT#(lf)#(lf) CASE#(lf)#(lf)            WHEN CANC_REASON = 'ESMH' THEN 'ESMH-Moving Home'#(lf)#(lf)            WHEN CANC_REASON = 'ESNA' THEN 'ESNA-No longer has appliance'#(lf)#(lf)            WHEN CANC_REASON = 'ESTC' THEN 'ESTC-T & C Restrictions'#(lf)#(lf)            WHEN CANC_REASON = 'ESPR' THEN 'ESPR-Price - Move to competitor/Too Expensive'#(lf)#(lf)            WHEN CANC_REASON = 'ESRC' THEN 'ESRC-Renewal Cancellation'#(lf)#(lf)            WHEN CANC_REASON = 'ESNB' THEN 'ESNB-New boiler / appliance'#(lf)#(lf)            WHEN CANC_REASON = 'ESNC' THEN 'ESNC-Cancellation previously not complete'#(lf)#(lf)            WHEN CANC_REASON = 'ESDA' THEN 'ESDA-Duplicate Account'#(lf)#(lf)            WHEN CANC_REASON = 'ESAI' THEN 'ESAI-Appointment Issues'#(lf)#(lf)            WHEN CANC_REASON = 'CSAI' THEN 'CSAI-Customer service agent issue'#(lf)#(lf)            WHEN CANC_REASON = 'RBPR' THEN 'RBPR-Removal of bundled product'#(lf)#(lf)            WHEN CANC_REASON = 'ENBE' THEN 'ENBE-Engineer behaviour'#(lf)#(lf)            WHEN CANC_REASON = 'ESAP' THEN 'ESAP-Alternative SP Product'#(lf)#(lf)            WHEN CANC_REASON = 'ESDE' THEN 'ESDE-Deceased'#(lf)#(lf)            WHEN CANC_REASON = 'ESFI' THEN 'ESFI-Failed Inspection'#(lf)#(lf)            WHEN CANC_REASON = 'ESCM' THEN 'ESCM-Change mind (cooling off)'#(lf)#(lf)            WHEN CANC_REASON = 'ESLI' THEN 'ESLI-Length of time to install'#(lf)#(lf)            WHEN CANC_REASON = 'ESSE' THEN 'ESSE-SAP Error'#(lf)#(lf)            WHEN CANC_REASON = 'ESBE' THEN 'ESBE-BER/ERC'#(lf)#(lf)            ELSE 'Other'#(lf)#(lf)        END AS FULL_CANCELLATION_REASON,#(lf)        CALLSTATS.SCOTTISHPOWER_SALESDATA.*#(lf)        FROM #(lf)CALLSTATS.SCOTTISHPOWER_SALESDATA#(lf)WHERE PROCESS <> 'DAG_LANDING_PAGE'#(lf)"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"PartnerNo", "SequenceKey", "Session_ID", "Interaction_ID", "Contact_Direction", "Contact_Method", "SESSION_TOKEN", "SESSION_STDT"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"CreatedAtTime", type time}, {"CreatedOnDate", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BPID"}, #"Scottish_Power_Extension- All (3)", {"BusinessPartnerID"}, "Scottish_Power_Extension- All (3)", JoinKind.Inner),
    #"Expanded Scottish_Power_Extension- All (3)" = Table.ExpandTableColumn(#"Merged Queries", "Scottish_Power_Extension- All (3)", {"CoCode", "SchCode", "PlanRef", "ProductName", "SaleType", "ContractStartDate", "ApplianceType", "ApplianceMake"}, {"CoCode", "SchCode", "PlanRef", "ProductName", "SaleType", "ContractStartDate", "ApplianceType", "ApplianceMake"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Scottish_Power_Extension- All (3)", {"PlanRef", "BPID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "IDKEY", each [NTLOGIN]&Number.ToText(Date.Year([ContractStartDate]))&Number.ToText(Date.Month([ContractStartDate]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"IDKEY", type text}}),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type1", {"IDKEY"}, BAUIDS, {"IDKEY"}, "BAUIDS", JoinKind.LeftOuter),
    #"Expanded BAUIDS" = Table.ExpandTableColumn(#"Merged Queries1", "BAUIDS", {"Name", "Organization", "Source", "Supervisor"}, {"Name", "Organization", "Source", "Supervisor"}),
    #"Sorted Rows" = Table.Sort(#"Expanded BAUIDS",{{"CoCode", Order.Ascending}, {"SchCode", Order.Ascending}, {"PlanRef", Order.Ascending}}),
    #"Added Custom2" = Table.AddColumn(#"Sorted Rows", "Canc_Date_Proper", each Text.Start([CANCELLATION_DATE],10)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Canc_Date_Proper", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each if [ContractStartDate] > [Canc_Date_Proper] then 1 else 0),
    #"Merged Queries2" = Table.NestedJoin(#"Added Custom1", {"BPID"}, ScottishPowerFutureCancellation, {"BusinessPartnerID"}, "ScottishPowerFutureCancellation", JoinKind.LeftOuter),
    #"Expanded ScottishPowerFutureCancellation" = Table.ExpandTableColumn(#"Merged Queries2", "ScottishPowerFutureCancellation", {"ContractEndDate", "DateReceived"}, {"ContractEndDate", "DateReceived"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ScottishPowerFutureCancellation", each [BPID] = "1000028885" or [BPID] = "1109596600"or [BPID] = "1106350852"),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"FULL_CANCELLATION_REASON", "AccountNo", "PremiseNo", "CreatedOnDate", "CreatedAtTime", "CreatedBy", "EURO_ID", "CHANNEL", "status", "CA", "PROCESS", "NEW_SALE_DT", "ACQUI_PLAN_STDT", "BPID", "PROD_SOLD", "ProductName", "Index", "PRODCHNG_PLAN_STDT", "CANC_STAT", "CANC_REASON", "PRODUCT_CANCELLED", "CANCELLATION_DATE", "NTLOGIN", "CoCode", "SchCode", "PlanRef", "SaleType", "ContractStartDate", "ApplianceType", "ApplianceMake", "IDKEY", "Name", "Organization", "Source", "Supervisor", "Canc_Date_Proper", "Custom", "ContractEndDate", "DateReceived"}),

    GroupedRows = Table.Group(#"Reordered Columns", {"BPID", "PROD_SOLD"}, {{"All", each Table.Distinct(Table.SelectRows(_, (x)=> x[PROD_SOLD] = x[ProductName]), {"ProductName"}), type table }}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

12 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Anonymous, you have only UNIQUE values in ProductName column... (so no duplicates!) 🙂

     

    If you want to extract last value of such group, you can do it like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiMLCwtTJR0l1+B4J39PH9cgKDsoON7D0cdNKVaHsEK3UB8fohQaGRgZEqvQiLBCMDve2d8vRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BPID = _t, PROD_SOLD = _t, ProductName = _t]),
    GroupedRows = Table.Group(Source, {"BPID", "PROD_SOLD"}, {{"All", each Table.LastN(_, 1), type table }}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi dufoq3 

       

      yes correct no duplicates in the Product Name column, Sorry The requirement is to keep the same value in Product name as it appears PROD_SOLD so to explain, Remove duplicates from BPID and PROD_SOLD and keep the row in Product Name that has same name in PROD_SOLD column and in this case it is the ES_BOILER_CONT_SUB_RT

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago
        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiMLCwtTJR0l1+B4J39PH9cgKDsoON7D0cdNKVaHsEK3UB8fohQaGRgZEqvQiLBCCDs2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BPID = _t, PROD_SOLD = _t, ProductName = _t]),
    GroupedRows = Table.Group(Source, {"BPID", "PROD_SOLD"}, {{"All", each Table.Distinct(Table.SelectRows(_, (x)=> x[PROD_SOLD] = x[ProductName]), {"ProductName"}), type table }}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined