Forum Discussion
Helper IFilter SQL view data in Power Query
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.
- Load all the data, including those 'Not Provided' records.
- 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.
Super UserThat error message points to a different column as the culprit. Which of your view columns is set to Integer?
MiaSunnyThank 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.