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 tri...
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
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Appreciate your reply, I did tried to opend the link at the bottom where it says Check How to use my query., Since I have got more steps preceding this, Could you please show me the method via the Power Query UI way (I am not that expert in Power query) to pick everything via the code apologies, Thanks