Filter SQL view data in Power Query

[MiaSunny]

I have a sql view from other department that I cannot check the code or modify only select permission. 

(the best practice is filtering and shaping the data from SQL to fit the needs while i have no permission. )

By using "select * from viewname where date > '2024-6-30' " i get errors below, but it works with "Select * from viewname" : 

I try to get the data filtered from Power Query and as expected it shows a row with errors at the bottom. 

I add a step to remove the errors and it doesn't work, the error msg is the same msg from SQL. 

Though I cannot get or modify the database, could we have a better solution other than just use it, write measures to calculate the result, or shape the table in DAX. 

Thank you for reading and any suggestions if you have . 

[Claude_Xu · Accepted solution]

Hi, it looks like the view in the database uses a varchar column to hold date type, and string literal 'Not Provided' is put in the column for missing case, instead of using a date type column with null allowed. If that's the case, to fix the issue in the source is the best solution.

If that's impossbile for whatever reason, you can load the data and do cleaning & transformation with M.

1. Load all the data, including those 'Not Provided' records.
2. Use Table.SelectRows to select the rows you need. One dummy solution is to define a function to check that date column and only returns true if the column contains a date value text, and that date text is greater than 20240630

That't my two cents. You may have another implementation for loading and filtering the data with M. 

[MiaSunny]

It took me some time to get all the data and I figured out the issue in some columns -- so many columns like 70+ and 20+ are int, crushed again and again with my 32G memory lol . 

And it works. Thank you so much for your help. 

[PwerQueryKees]

The 1st screenshot of the error shows the last bit of a larger query. That query seems to be a translation of the sql you used, since the column names and syntax are different.

Am I right?

Can you show all of the transformed query?

[MiaSunny]

Thank you! you are pretty right and it helps. errors are always from data type unmatching. 

[lbendlin]

That error message points to a different column as the culprit. Which of your view columns is set to Integer?

[MiaSunny]

Thank you for your reply, i could understand there is a column set as varchar and includes data as text and "Not provided", since i cannot modify the view I could only seek for steps on Power Query.

There are like 70+ columns and 1/3 of them are INT. since my where condition is adding to the date column in sql which caused the error, I tried to run select * from table where date = "Not provided" but got the same error. 

I know it's not a good design even not relational for the view which I cannot change it. 

[lbendlin]

Read the entire view in import mode as text , then apply column types and decide what to do with errors (replace errors or remove error rows).

[MiaSunny]

Thank you ! finally pulled all the data from SQL and got the error-causing columns, and replaced the errors as blanks to keep other information.