none
Create a formula (add step) to remove dulplicates, but not blanks RRS feed

  • Question

  • I have a [ColumnName] that I need to remove duplicate values from, but can not remove the blank records. I'd like to add a step in power query. Any help would be appreciated!
    Tuesday, May 7, 2019 10:55 AM

Answers

  • Hi,

    You may use following code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        distinct = Table.Distinct(Source, {"ColumnName"}) & Table.SelectRows(Source, each [ColumnName] = null)
    in
        distinct

    Or, if you need to keep the order of rows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        i = Table.AddIndexColumn(Source, "i", 0, 1),
        list = List.Buffer(List.Union({Table.SelectRows(i, each [ColumnName] = null)[i], Table.Distinct(i, {"ColumnName"})[i]})),
        filter = Table.SelectRows(i, each List.Contains(list, [i]))
    in
        filter
    • Edited by Aleksei Zhigulin Tuesday, May 7, 2019 11:31 AM
    • Proposed as answer by anthony34 Tuesday, May 7, 2019 2:41 PM
    • Marked as answer by patri0t82 Tuesday, May 7, 2019 4:35 PM
    Tuesday, May 7, 2019 11:29 AM

All replies

  • Hi,

    You may use following code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        distinct = Table.Distinct(Source, {"ColumnName"}) & Table.SelectRows(Source, each [ColumnName] = null)
    in
        distinct

    Or, if you need to keep the order of rows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        i = Table.AddIndexColumn(Source, "i", 0, 1),
        list = List.Buffer(List.Union({Table.SelectRows(i, each [ColumnName] = null)[i], Table.Distinct(i, {"ColumnName"})[i]})),
        filter = Table.SelectRows(i, each List.Contains(list, [i]))
    in
        filter
    • Edited by Aleksei Zhigulin Tuesday, May 7, 2019 11:31 AM
    • Proposed as answer by anthony34 Tuesday, May 7, 2019 2:41 PM
    • Marked as answer by patri0t82 Tuesday, May 7, 2019 4:35 PM
    Tuesday, May 7, 2019 11:29 AM
  • Hi, I really appreciate your answer, but I'm having a hard time figuring out how to add it. I was thinking I'd click the formula button beside the formula bar. I'm not quite as fluent in Power Query as I'd like to be. Would you be able to help me understand a little better?

    Thanks

    I know this is obviously not correct, but was my first instinct.


    • Edited by patri0t82 Tuesday, May 7, 2019 2:49 PM
    Tuesday, May 7, 2019 2:46 PM
  • It's look like you should cut #"Added Conditional Column" (presumably, it's your last step) and paste it (twice) instead of Source in your formula.
    Tuesday, May 7, 2019 4:02 PM
  • Thanks for the reply. This is the best I could come up with

    = #"Added Conditional Column" Table.Distinct("Added Conditional Column", {"Action Item Number"}) & Table.SelectRows("Added Conditional Column", each ["Action Item Number"] = null)

    though, it understandably doesn't work.

    Tuesday, May 7, 2019 4:24 PM
  • Oh wait, I got it to accept this:

    = Table.Distinct(#"Added Conditional Column", {"Action Item Number"}) & Table.SelectRows(#"Added Conditional Column", each [Action Item Number] = null)

    With that said, it didn't do what I hoped. I'm only looking for it to remove one duplicate, however it removed all the blanks still.

    Tuesday, May 7, 2019 4:33 PM
  • Aha! I replaced all my blanks with null, and that has appeared to fix the issue. Thank you for your help!
    Tuesday, May 7, 2019 4:35 PM