Forum Discussion
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 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
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:
In Power BI Desktop, go to the "Home" tab.
Click on "Edit Queries" to open the Power Query Editor.
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.
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.
Close the Power Query Editor and apply the changes.
2 Replies
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:
In Power BI Desktop, go to the "Home" tab.
Click on "Edit Queries" to open the Power Query Editor.
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.
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.
Close the Power Query Editor and apply the changes.
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.
