quantfinRguy's avatar
quantfinRguy
Frequent Visitor
8 years ago
Status:
Needs Info

Rows disappearing between Power Query and data model

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)

 

 

 

Data Modeling

5 Comments

  • quantfinRguy's avatar
    quantfinRguy
    Frequent Visitor
    8 years ago

    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's avatar
    v-haibl-msft
    Microsoft Employee
    8 years ago

    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

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    8 years ago

    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

Completed Ideas