Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How do I rollup duplicate rows into one row?

I have a report with duplicate rows that I need to rollup into one row. The rows have a Member ID that I would liked rolled up (grouped by?). The top table shows the rows as they are now. I need to roll them up into one row as in the bottom table. There are no calculations or math involved. I would like to rollup(group) by the Member ID.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I found out the issue. The fields Diabetes, COPD, Asthma, CAD, and HF are not fields collected by Power BI. There are custom columns created by a funtion (IF statements). They don't show up in Power Query. 

     

    I was able to rollup the rows by exporting the table to CSV and loading the CSV file into Excel. I then ran Power Query in Excel to roll up the rows.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Select all columns other than Diabetes, COPD, Asthma, CAD and HF, right click and select "Unpivot Other columns",  Select the Attribute column and go to Transform > Pivot.  See the image below

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I found out the issue. The fields Diabetes, COPD, Asthma, CAD, and HF are not fields collected by Power BI. There are custom columns created by a funtion (IF statements). They don't show up in Power Query. 

       

      I was able to rollup the rows by exporting the table to CSV and loading the CSV file into Excel. I then ran Power Query in Excel to roll up the rows.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , take min of max of the column Diabetes, COPD, ASTHMA, HF

     

    or measure like

    firstnonblank([Diabetes], blank())

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    Try to create a calculate table below:

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Member ID],
    'Table'[Primary Condition],
    'Table'[Primary Condition Risk],
    'Table'[Primary Condition ldentified Date],
    'Table'[Update Date],
    "Diabetes", MAX ( 'Table'[Diabetes] ),
    "COPD", MAX ( 'Table'[ COPD] ),
    "Asthma", MAX ( 'Table'[Asthma] ),
    "CAD", MAX ( 'Table'[CAD] ),
    "HF", MAX ( 'Table'[ HF] )
)

    Result:

    Please refer to the attachment below for details

     

     

    Is this the result you want? Hope this is useful to you

    Please feel free to let me know If you have further questions

     

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