Remove one row in Power Query Editor based on condition

[gauravkumar16]

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 have much effect on size of data model.

But I want this because when viewing column profile, Min value is shows zero which is incorrect.

It is true that if I want to know the minimun amount, I can sort the column with ascending order and see. But I was hoping if it can be corrected so that column profile shows the real Min value.

Thanks

[_elbpower · Accepted solution]

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]

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.