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

[shaikhzdandg]

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 

[dufoq3]

Hi @shaikhzdandg, 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

[shaikhzdandg]

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]

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

[p45cal]

Not sure about this but does this not give the same result:?

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]),
    FilteredRows = Table.SelectRows(Source, each [ProductName] = [PROD_SOLD]),
    RemovedDuplicates = Table.Distinct(FilteredRows, {"BPID", "PROD_SOLD"})
in
    RemovedDuplicates

[shaikhzdandg]

I pastet above query into the Advanced Editor at the very last line of my existing query and it throws an erros shown below

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(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

[dufoq3 · Accepted solution]

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

[shaikhzdandg]

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 

[dufoq3]

Paste my code into blank query and replace Source step code with your table reference: (so replace Table.FromRows(Json.Document.... with YouryTableName)

[shaikhzdandg]

okay Thanks I pasted the code in Blank Query and replaced the Source step with original table, it returns no result after the last step, I checked my orginal query and NO FILTERS applied let
Source = #"Scottish_PowerSales using MERGE Test",
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

[dufoq3]

I see different column names in your latest screenshot.

[shaikhzdandg]

Hi @dufoq3  Just to provide you with more context this is what we want, we only want to retain the Yellow highlighted rows and remove the rest in the final output, here what I have done is sorted the BPID (in ASC order) then CONTRACT_START?_DATE (in Desc order) so the rows to keep are the ones at the top of each column 

and below is what I am expecting in the output

[shaikhzdandg]

ooh, Sorry I have more columns in my table then what I pasted in my first iniital screen shot, below are column in my main table (and few more which cant be seen in screen shot)