Native Query > Query not folding after filtered rows

[rajulshah]

Hello everyone,

I have SQL datasource and fetching just one table.
I want to apply Incremental Refresh on the table. Hence, I created 2 parameters - RangeStart & RangeEnd and also both, parameters and the date field both have Date/Time as datatype.

So, when I do all the transformations, I am able to see 'View Native Query' as enabled. As soon as I filter the field with these parameters, it becomes disabled.

So I tried following alternatives:

1. I tried to filter the field with just the date values, but I found very weird behaviour. Whenever I had "1st July 2021", it used to show as enabled. But whenever I tried other date value, let's say, "26th June 2021", it again got disabled.
2. I tried the solution given in this blog but it used to work with a datevalue and didn't work with the Incremental Refresh parameters.
3. I checked out the video by Patrick but I am not able to get the queries as mentioned by him.

Does anyone have faced this issue or can guide me through it?

Any help would be great.

Thanks in advance.

[mwegener]

Hi @rajulshah ,

what kind of database is your data source? (MS SQL?)

To me the "Native Query" is more of an indicator, can you check on the database what query Power BI is really sending?

[rajulshah]

Hello @mwegener , thank you for the response.

Yes, the query sent to SQL Server has this filter applied, then also it would load all the data in Power BI and then filter it in there.

[mwegener]

Hi @rajulshah ,

if the filters are present in the SQL query, the query folding will also work and only the data that is needed will be transferred from SQL Server to Power BI.

[rajulshah]

@mwegener ,

Yes, but what it does is loads all the rows in the Power BI, let's say 10 million and then applies filter to it and in the end, the table will have only filtered rows, i.e 2 million rows.

do you know why it does this?

[mwegener]

@rajulshah 

When Power BI loads all rows, the filter is not in the WHERE clause of the SQL.
Can you post screenshots of the Power Query Steps and the SQL.

[rajulshah]

Sure,

Below are the steps of our query:

But instead of the parameters, even if we use hardcoded numbers, it loads all the data and then filters in Power BI. Also, I have used view, but the native query does not have the filter conditions in it.

Am I doing anything wrong? Please guide.

[mwegener]

Hi @rajulshah ,

to my knowledge the "Changed Type" step breaks the query folding. Why does the data type need to be changed here?

[rajulshah]

Hello @mwegener ,

We are fetching the Date in number format but it is not imported as "Whole Number" but in text format.

Hence, we are changing the formats. Moreover, by clicking on this step, I am able to see "Native Query" but not on the next step.

[mwegener]

Hi @rajulshah ,

maybe you can try the following.

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [PurchaseDate] >= Number.FromText(DateTime.ToText(RangeStart, "yyyyMMdd")) and [PurchaseDate] < Number.FromText(DateTime.ToText(RangeEnd, "yyyyMMdd")))

[mwegener]

it unsettles me that Power Query doesn't recognize the column's data type correctly, but never mind.

Can you post a screenshot of the SQL statement as Partrick shows it in his video?
Can you post your function ConvetDateToKey?

[rajulshah]

Hello @mwegener ,

Thank you for the response, and sorry for the late response.

We have already done that using the ConvertDateKey function as shown in Patrick's video.

Do you have any idea whether Query Folding is done in a view or not?
Is there any chance that Query Folding isn't happening because we have used a view?

[mwegener]

Hi @rajulshah ,

Query Folding is also executed on a view, as Power Query queries the database object.

At the end of the day, Power Query doesn't care if it has to query a table or a view, since the SQL is the same.

[rajulshah]

@mwegener ,

Thanks for the response. I don't know what the issue maybe.