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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
3 years ago

Hi, Anonymous ;

You could use matrix in power bi not table.

Then set it.

off total.

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 Yalan Wu,

Thank you very much for your detailed instructions. I have tried this previously but it did not meet my business users. They want Loading Dates (if more than 1) are being merged into one cell, summerized [Netweight Shippled] and Groupped by BatchNo as my post above. Because their purpose is to export to Excel file for further analysis. Therefore they have asked me to do so on Power BI. The solution of pbi-novice is closer to my goal but we need to get distinct [Loading Date].