Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Losing Rows after applying a filter on a Column in Edit Queries

Hi,

 

I am facing  a peculiar issue in power BI Querying.

 

Suppose i have a table that is fetched form Odata feed. The table has 1000 rows and the table has a column called "TEST" which has values a,b,c,d,e which is spread across all the 1000 rows.

When I load the table to an Excel sheet by copying the table from Power BI to an excel sheet and apply the filter on the column so that i filter out TEST column for "a". Say we get 200 rows which have "a".

 

When i do the same in edit queries mode and i filter the TEST column  for "a" in the query editor window and then load the table i get  less rows. Around 180 rows which have "a".

 

I tried writing the query in excel Power query too but the result was the same i lose rows whenever i apply the filters in power Query.

 

Any suggestions on why this is happening will help.

 

Regards,

 

Parvez 

Status: Needs Info
Comments
v-haibl-msft
Employee

@Parvez_Raja

 

Are you using the latest May 2017 version of Power BI Desktop? Do you have duplicate rows in the original table? If yes, after you filter the table and drag the columns into Table visual in the canvas, the duplicate rows will not be displayed as below.

 

Losing Rows after applying a filter on a Column in Edit Queries_1.jpg

 

If it is not the same scenario as above, are you able to repro the same issue when the data source is excel file? If you can, could you please provide the excel file to me so that I can have a try locally? Some repro screenshots are preferred.

 

Best Regards,
Herbert

Vicky_Song
Impactful Individual
Status changed to: Needs Info
 
Parvez_Raja
Regular Visitor

@v-haibl-msft Thanks for responding. Let me be more clear. I have a table of 15000 rows with a column called order id which has 1400 distinct values. There are 2 columns called payment type and price in the above table. I want to group them by order ID and calculate the sum of Price . The payment type column has 2 distinct values called upfront and credit.

I want two separate tables i.e. revenue upfront and revenue credit from the above one with filter applied on the payment type column in the query editor window. so ideally the number of order ids in both the tables should add upto 1400.

 

Here lies my problem when i sum the number of order ids in both table i get 1005 rows instead of 1400 and a few order ids are not there in either of the tables , even when they have their payment types either as credit or upfront. They are just ignored and filtered out.

 

This is all done using the latest May version of Power BI

 

Edit: Another important thing the source for the power bi file is an odata feed. I tried loading this odata feed data to an excel sheet and took the source for the Power bI file as this excel sheet and everything is fine.

Its only when i take the source as  an odata feed directly that this problem arises.

 

 

Regards,

Parvez

Parvez_Raja
Regular Visitor
achinm45
Advocate IV

Hi @Parvez_Raja,

Can you try following :

1) Before putting condition for filteration, you can put some operation like grouping or an operation like adding a news test derived columns. Do for both.

2) Make two tables and load them into DAX and group them separately.

 

I can think of these steps as of now. 

 

Hope it helps.