Forum Discussion

gauravkumar16's avatar
gauravkumar16
Frequent Visitor
2 years ago
Solved

Remove one row in Power Query Editor based on condition

Hello community,   In my data there is one row where amount is zero. I want to remove this row in Power Query Editor because this is not required. I know it is just one row so removing it will not...
Need Help
  • _elbpower's avatar
    _elbpower
    2 years ago

     

    Create following DAX measure in your report.

    MinimumAmountGreaterThanZero = MINX(FILTER('YourTableName', 'YourTableName'[Amount] > 0), 'YourTableName'[Amount])

    OR

    You can also achive the removal of zero records in the power query by following below steps:

    1. In Power BI Desktop, go to the "Home" tab.

    2. Click on "Edit Queries" to open the Power Query Editor.

    3. In the Power Query Editor:

      • Locate the table that contains the 'Amount' column in the "Queries" pane on the left.
      • Click on the filter icon (funnel icon) next to the 'Amount' column header.

    4. In the filter dialog box:

      • Select "is not equal to" or "does not equal" depending on the specific interface of your Power Query version.
      • Enter "0" as the value.
      • Click the OK or Apply button to filter out the records with an amount equal to 0.

    5. Close the Power Query Editor and apply the changes.

     

_elbpower's avatar
_elbpower
Resolver III
2 years ago

 

Create following DAX measure in your report.

MinimumAmountGreaterThanZero = MINX(FILTER('YourTableName', 'YourTableName'[Amount] > 0), 'YourTableName'[Amount])

OR

You can also achive the removal of zero records in the power query by following below steps:

  1. In Power BI Desktop, go to the "Home" tab.

  2. Click on "Edit Queries" to open the Power Query Editor.

  3. In the Power Query Editor:

    • Locate the table that contains the 'Amount' column in the "Queries" pane on the left.
    • Click on the filter icon (funnel icon) next to the 'Amount' column header.

  4. In the filter dialog box:

    • Select "is not equal to" or "does not equal" depending on the specific interface of your Power Query version.
    • Enter "0" as the value.
    • Click the OK or Apply button to filter out the records with an amount equal to 0.

  5. Close the Power Query Editor and apply the changes.

 

gauravkumar16's avatar
gauravkumar16
Frequent Visitor
2 years ago

Thanks elvin for reply.

 

Actally, I just had to unselect that particular row in Power Query.

This will ensure column profile shows correct statistics and also that particular row will not load into data model.