Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Finding Duplicates

    Posted 29 days ago
    Hello everyone!

    I thought it might be helpful to provide a little context before asking my question. Retailers order items my company makes and we ship it to them. Then the shipping company sends us an invoice. We create a Purchase Order (PO) for that invoice and tie it to the Sales Order (SO) the customer sent in so we can keep track of shipping costs by customer. Very often, we get multiple multiple Purchase Orders for the same Sales Order and we need to delete one out of the system.


    Now my question! I have a simple report that calls out the duplicates so we can easily find and delete them. I've attached screen shots below of the Purchase Order list and the list where it calls out the duplicates. As you can see it works well except for SO112770 and SO112771 which are both duplicates but not picked up on the duplicate list because they were combined on one PO. The order number has to be EXACTLY the same to catch the duplicate which does not always happen. Is there a way in Power BI to catch these duplicates too?


    Thanks,
    Calen Butler


  • 2.  RE: Finding Duplicates

    Posted 28 days ago
    Olá
    Se não há critérios para remover os duplicados, uma alternativa no Power Query seria:

    1. Adicionar uma coluna contando a quantidade de caracteres;
    2. Em seguida fazer um filtro para manter apenas aquelas linhas que contêm 9 caracteres


    ------------------------------
    Vilmar Santos
    ------------------------------



  • 3.  RE: Finding Duplicates

    Posted 28 days ago
    Can you in the power query area filter for a space, and generate a second row or rows separating the purchase orders?   That would transform your single row with two items to two rows with one item each.  If it's possible for three items to come together you might have to repeat that step.

    ------------------------------
    Elena Schott
    Sr. Business Analyst
    Aon - San Francisco, CA
    ------------------------------



  • 4.  RE: Finding Duplicates

    Posted 24 days ago
    I tried this and it worked well. Thank you for the suggestion!

    ------------------------------
    Calen Butler
    Director of FP&A
    Garner Foods
    Winston-Salem NC
    ------------------------------



  • 5.  RE: Finding Duplicates
    Best Answer

    Bronze Contributor
    Posted 28 days ago
      |   view attached
    Let's try to solve it with DAX first. With CONTAINSSTRING we can search for sales orders as substrings of other sales orders:

    Sales Order Count = 
    VAR CurrentSO =
        SELECTEDVALUE ( Table[Vendor Order Number] )
    VAR AllSO =
        ALL ( Table )
    VAR FilteredSO =
        FILTER (
            AllSO,
            CONTAINSSTRING ( CurrentSO, Table[Vendor Order Number] )
                || CONTAINSSTRING ( Table[Vendor Order Number], CurrentSO )
        )
    VAR Result =
        IF ( ISINSCOPE ( Table[Vendor Order Number] ), COUNTROWS ( FilteredSO ) )
    RETURN
        Result​
    This gives the following result:
    There is one serious limitation with this solution. If a SO that is a part of a string of SO's has a duplicate in another non-identical string of SO's it will not be counted twice.

    To overcome this, I agree with the others that we will need to do some transformations in the query editor. My suggestion is to use to use the Text.Split function to convert the strings of sales orders into lists. Then the lists can be expanded into new rows:

    #"Split Text by Delimiter" = Table.TransformColumns(
        Source,
        {"Vendor Order Number", each Text.Split(_, " ")}
      ),
      #"Expanded Vendor Order Number" = Table.ExpandListColumn(
        #"Split Text by Delimiter",
        "Vendor Order Number"
      )​

    Both methods are demonstrated in the attached file.

    ------------------------------
    Tomas
    ------------------------------

    Attachment(s)

    pbix
    Finding Duplicates.pbix   23 KB 1 version


  • 6.  RE: Finding Duplicates

    Posted 24 days ago
    Thank you so much for the suggestion and the code. I was able to use your code in the 2nd suggestion to edit my query and now it's working!

    ------------------------------
    Calen Butler
    Director of FP&A
    Garner Foods
    Winston-Salem NC
    ------------------------------