Limit data on import

[ameerehs]

Hi there,

What can I add to filter my data before it is imported into power bi. Currently I have a filter once the data is imported but my power bi has become very slow and laggy. See below for my query. Any help would be appreciated. Thanks

let
Source = Sql.Database("POLGUELPH02WFDB", "PolyconIntegrative"),
dbo_p_igr_historian_data = Source{[Schema="dbo",Item="p_igr_historian_data"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_p_igr_historian_data, each Date.IsInPreviousNDays([TIMESTAMP], 35)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each true),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "Custom", each if Text.Contains([PLCSHORTTAGNAME], "F1.vaI_Iavg")

[edhans]

First, delete the Filtered Rows1 step. It is doing nothing.

As to why your query is laggy, not sure. The filter should be folding to the server. When I created a filter to limit it to the last 15,000 days (just messing around) I can right-click on that filter and View Native Query has this in it:

...
where [_].[Due Date] >= convert(datetime2, '1980-06-12 00:00:00') and [_].[Due Date] < convert(datetime2, '2021-07-07 00:00:00')

So the server is filtering the data. For you it should be just doing 35 days.

I am not sure what the next step is doing. It might or might not fold but you cut it off.  This is not a valid statement:

Table.AddColumn(#"Filtered Rows1", "Custom", each if Text.Contains([PLCSHORTTAGNAME], "F1.vaI_Iavg")

You are missing a closing paren

You cannot have an if statement without both a then and else clause. Text.Contains is folding, but what is in your then/else clause might not be.

However, your query should be folding through the filtering, so if it is laggy, then even for 35 days, you may have millions of records.

Make sure your column profiling at the bottom of your screen says 1,000 rows, not "all data."

To help further, we'd need to see the full formula in the Table.AddColumn() step, as well as know if your filter is folding. Again, right-click on it, and see if View Native Query is available.

[ameerehs]

my full query is quite long as I am doing more filtering and rearranging of columns after..However while the query does filter by 35 days, when I apply changes it still loads over 20 million records, while 35 days are only 4.5 million

[edhans]

Then it isn't filtering correctly, but without data or something to look at, I cannot say. It isn't a bug, it is the way the filter is being used. Unfortunately I cannot help further without some hard data to look at, like the native query generated by the filter.