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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Hi FreemanZ, 
 
Thank you for your quick response,
Actually BatchNo and Loading Date are on two different tables, I have applied your DAX, but the result is still the same.
 
SumTable =
ADDCOLUMNS(
        SUMMARIZE(Fact_ShippingKPI, Fact_ShippingKPI[BatchNo], CompletionDate[DateBK]),
        "WeightShipped",
        CALCULATE(SUM(Fact_ShippingKPI[NetWeight])
    ))
 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I would like to provide the model

 

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago
    Try this:
     
    SumTable=
    VAR _table1 = SUMMARIZE(Fact_ShippingKPI, Fact_ShippingKPI[BatchNo])
    RETURN
    ADDCOLUMNS (
        _table1,
        "Loading Date"
        CALCULATE (
            CONCATENATEX(
                _table1,
                DIM_Date[DateSK],
                UNICHAR(10)
            ),
            "WeightShipped",
            CALCULATE(
                SUM(Fact_ShippingKPI[NetWeight])
            )
    )
    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      Anonymous If that still does not work, could you please simify your data and send me the pbix files.