Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MiaSunny
New Member

Filter SQL view data in Power Query

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" : 

MiaSunny_0-1725482372644.png

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

MiaSunny_1-1725482620715.png

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

MiaSunny_2-1725482707019.png

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 . 

1 ACCEPTED SOLUTION
Claude_Xu
Frequent Visitor

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. 

View solution in original post

8 REPLIES 8
Claude_Xu
Frequent Visitor

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. 

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. 

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?

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

lbendlin
Super User
Super User

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

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. 

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).

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors