cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
orlio1 Occasional Visitor
Occasional Visitor

Create new table based on duplicates in a colum

Hi All,  I am fairly new to Power BI and I am having a hard time trying to do something i thought would be very easy.  I have a table that contains sales information.  Each row is a different line from an invoice generated from our system.  If the invoice we generated had multiple products on it, it will list each product on its own row and use the same invoice number.  So under my invoice number column, I will have duplicates.

 

What I am trying to do is create a new table that only has the sales of invoices with multiple products.  So in essense,  I want to be able to identify duplicate invoice numbers in a column and put that in a separate table. 

 

Any help would be appreciated.  Also, I am new to this forum so if this is not the correct place to post this please let me know. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create new table based on duplicates in a colum

HI @orlio1,

 

I'd like a part of sample data to help us clear your data structure for coding formula.

 

Sample:

New Table =
VAR _list =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                SUMMARIZE (
                    Table,
                    [invoice ID],
                    "Product Count", COUNTROWS ( VALUES ( Table[Product] ) )
                ),
                [Product Count] > 1
            ),
            "ID", [invoice ID]
        )
    )
RETURN
    FILTER ( Table, [invoice ID] IN _list )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: Create new table based on duplicates in a colum

HI @orlio1,

 

I'd like a part of sample data to help us clear your data structure for coding formula.

 

Sample:

New Table =
VAR _list =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                SUMMARIZE (
                    Table,
                    [invoice ID],
                    "Product Count", COUNTROWS ( VALUES ( Table[Product] ) )
                ),
                [Product Count] > 1
            ),
            "ID", [invoice ID]
        )
    )
RETURN
    FILTER ( Table, [invoice ID] IN _list )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |