Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove rows from data

Hi Folks,

I have exported data from excel, PFB sample dataset.

ColAColBColC
aabb23
ccdd43
eeff30
 Total96
gghh10
iijj35
kkll20
 Total65

 

Now, I need to remove rows having Totals. So my data after transformation should look like below.

ColAColBColC
aabb23
ccdd43
eeff30
gghh10
iijj35
kkll20

 

Can someone please advise on how to achieve the same?

 

 

Thanks & Regards,

Nikita Dalela | BI Consultant

Nice Software Solutions

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We can use two ways to meet your requirement. One is in Power Query Editor, another is to create a calculate table.

     

    1. In Power Query Editor, we can create a conditional column. The “Total” is 0, others are 1.

     

     

     

     

    2. Then we filter out the value of 0 in custom column.

     

     

    3. At last we delete the custom column, the result like this,

     

     

    Or we can create a calculate table using DAX.

     

    Table 2 = SUMMARIZE(FILTER('Table (2)','Table (2)'[ColB]<>"Total"),'Table (2)'[ColA],'Table (2)'[ColB],'Table (2)'[ColC])

     

     

     

    If them don’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

8 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use two ways to meet your requirement. One is in Power Query Editor, another is to create a calculate table.

     

    1. In Power Query Editor, we can create a conditional column. The “Total” is 0, others are 1.

     

     

     

     

    2. Then we filter out the value of 0 in custom column.

     

     

    3. At last we delete the custom column, the result like this,

     

     

    Or we can create a calculate table using DAX.

     

    Table 2 = SUMMARIZE(FILTER('Table (2)','Table (2)'[ColB]<>"Total"),'Table (2)'[ColA],'Table (2)'[ColB],'Table (2)'[ColC])

     

     

     

    If them don’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi Anonymous 

     

    if you open up power query from the 'Transform data'-button:

     



    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

    Cheers,
    Sturla

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi sturlaws ,

      I am aware of "Remove Rows" in Power Query Editor, however there is no option to remove selected rows or specific rows. My data is huge and has multiple rows containing "Total".

      Kindly let me know if I am missing something here.

       

       

      Thanks & Regards,

      Nikita Dalela | BI Consultant

      Nice Software Solutions

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        sorry, obviously did not manage to include a screenshot: