Combining duplicate rows and add column for different values

[GabrielFz]

Hi,

I'm trying to get unique rows for one of my Data Sources. The data looks like this:

And I would like to get a table like this:

Notes:

On my original data, not every product has 2 lines

I have several columns with this same purpose, so I would like a solution that will work on a bigger database

Thanks in advance (:

[amitchandak · Accepted solution]

@GabrielFz , create a new table like

	summarize(														
selectcolumns(Table,"Product", Table[product],"Quantity A",if(Table[Location] ="A",Table[Quantity],blank())
											,"Quantity B",if(Table[Location] ="B",Table[Quantity],blank())
											,"Delivery Date A",if(Table[Location] ="A",Table[Delivery Date],blank())
											,"Delivery Date B",if(Table[Location] ="B",Table[Delivery Date],blank())
											,"Color", table[Color]),[product],[Color],"Quantity A",sum([Quantity A]),"Quantity B",sum([Quantity B])
											,"Delivery Date A",max([Delivery Date A]),"Delivery Date B",Max([Delivery Date B]))

[Icey · Accepted solution]

Hi @GabrielFz ,

Based on my test, the expression @amitchandak provided is a bit problematic. Please modify it like so:

Table 2 =
VAR t =
    SELECTCOLUMNS (
        'Table',
        "Product", 'Table'[product],
        "Quantity A", IF ( 'Table'[Location] = "A", 'Table'[Quantity], BLANK () ),
        "Quantity B", IF ( 'Table'[Location] = "B", 'Table'[Quantity], BLANK () ),
        "Delivery Date A", IF ( 'Table'[Location] = "A", 'Table'[Delivery Date], BLANK () ),
        "Delivery Date B", IF ( 'Table'[Location] = "B", 'Table'[Delivery Date], BLANK () ),
        "Color", 'Table'[Color]
    )
RETURN
    SUMMARIZE (
        t,
        [product],
        [Color],
        "Quantity A", SUMX ( t, [Quantity A] ),
        "Quantity B", SUMX ( t, [Quantity B] ),
        "Delivery Date A", MAXX ( t, [Delivery Date A] ),
        "Delivery Date B", MAXX ( t, [Delivery Date B] )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@GabrielFz , create a new table like

	summarize(														
selectcolumns(Table,"Product", Table[product],"Quantity A",if(Table[Location] ="A",Table[Quantity],blank())
											,"Quantity B",if(Table[Location] ="B",Table[Quantity],blank())
											,"Delivery Date A",if(Table[Location] ="A",Table[Delivery Date],blank())
											,"Delivery Date B",if(Table[Location] ="B",Table[Delivery Date],blank())
											,"Color", table[Color]),[product],[Color],"Quantity A",sum([Quantity A]),"Quantity B",sum([Quantity B])
											,"Delivery Date A",max([Delivery Date A]),"Delivery Date B",Max([Delivery Date B]))