Rows disappearing between Power Query and data model

[quantfinRguy]

223 is the row in question. You can see the value is a 0. Note the "Manager" column is filtered to this one specific manager right now, not all values.

The row shows up in the data model. 

But when I remove the row filter in Power Query...

The row disappears in the data model (look at the dates).

This is causing issues since I'm running a validation where my measure returns BLANK() if _x_ number of trailing months are not there. (i.e., March, April, and May all return BLANK() because it's checking if I have any missing values in the previous quarter)

[quantfinRguy]

Note:

Not all zero values were removed. There are still many present in the data. I have not run into any other rows being omitted, but I haven't audited the entire data set. 

[v-haibl-msft]

@quantfinRguy

I cannot repro the same issue with similar data on my side. Could you please share your PBIX file through online file service like OneDrive?

Best Regards,

Herbert

[quantfinRguy]

See my reply here for the data to reproduce the error:

http://community.powerbi.com/t5/Desktop/Power-BI-deleting-non-empty-rows-can-t-find-fix/m-p/269275#M...

[v-haibl-msft]

@quantfinRguy

I tried to repro by reading csv data through r script but still could not repro the same issue on my side. Please refer to following screenshot and the power queries.

Maybe you can try with the latest version 2.51.4885.701 (October 2017) to see if it helps.

You can change the type of Date column from Text to Date and sort it descending, so that you can find it easier in Data view.

let
    Source = R.Execute("library(reshape)#(lf)#(lf)returns <- read.csv(""C:/Data Scrambled.csv"") # enter your file path here#(lf)returns <- melt(returns, id.vars = ""Date"")#(lf)returns <- na.omit(returns)"),
    returns1 = Source{[Name="returns"]}[Value]
in
    returns1

Best Regards,
Herbert