Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Merging multiple rows based on condition and aggregation

Hello Communtiy,   I having an requirement from business users and need your help. From the screenshot, we have BatchNo column, for example the BatchNo HL18002040, business users want to merge Loa...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, Anonymous ;

    Sorry for the late reply, We could merge in powe query.

    Add funtion.

    = Table.Group(#"Changed Type", {"Batch"}, {{"Netweight", each List.Sum([NetWeight]), type nullable number}, {"a", each Text.Combine(  [Date]," 
"), type nullable date}})

    The final show:

     


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

pbi-novice's avatar
pbi-novice
Helper I
3 years ago

I found a solution however, this will turn your dates from "Date" format to "String" format. I don't really see a solution otherwise. 

 

Table 2 is a calculated table that groups the original table by "BatchNo"(ID) and sums the "NetWeightShipped"(VALUE)

 

Table 2 = GROUPBY('Table','Table'[ID], "Total", SUMX(CURRENTGROUP(),'Table'[Value]))
 
 
Column is a calculated column that concatenates all date values with a "New Line" charachter per "BatchNo"(ID). Table.[Date] = "Loading Date"
 
Column = CONCATENATEX(FILTER('Table', 'Table'[ID] = 'Table 2'[Table_ID]), 'Table'[Date].[Date], "
")
 
Notes:
 
This assumes that the original table does not have duplicate Loading Date per BatchNo. I believe my DAX expressions will duplicate dates for each occurance per BatchNo.
 
Also, the fact that your dates are in a different table does not matter, as long as it is related to the table properly.
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi pbi-novice,

I have applied your DAX code, the  "Table 2" works well but "Loading Date" concatenates all the values (screenshot) of a specific BatchNo in the Fact_ShippingKPI table. Working around for using functions such  DISTINCT, ALLNOBLANK, VALUES for Loading Date but not resolved.

 

Table 2 = GROUPBY('Fact_ShippingKPI',Fact_ShippingKPI[BatchNo], "NetWeigh Shipped", SUMX(CURRENTGROUP(),Fact_ShippingKPI[NetWeight]))
 
Loading Date = CONCATENATEX(FILTER('Fact_ShippingKPI', 'Fact_ShippingKPI'[BatchNo] = 'Table 2'[Fact_ShippingKPI_BatchNo]), RELATED(Dim_Date[DateBK])," ")