Solved: Import data from SQL without store procedure

rwlsxn · ‎02-27-2023

Hey,

First time posting here so please be forgiving if my wording or explanations aren't exact.

I'm trying to get a specific column from SQL through Power BI.

I'm using Import, as DirectQuery doesn't fit what I need to do to the data in the long run. There will be a LOT of transformation done in PBI (which I know is not best practice, but it will cost a fortune and take months to get our admins to build me a view with all the transformation included in SQL and unfortunately it's just not a feasible option for our team). The data in SQL is just too large for PBI to handle. I can't even get it to load in! I waited 5 hours one day and had to cancel the load as two of the tables were showing as over 25mil rows each!

This means I need to be able to filter the data before it even comes in, based on dates.

I've tried to start off simple, as I've not used SQL very much in my PBI career. Firstly, I'm just trying to select the distinct values from one column in a view table in SQL. I've done the following:
Filled in the server name, and database, selected Import, and wrote in the advanced editor:

"SQL SELECT DISTINCT LOADDATE
from Table_Name"

but I'm getting a stored procedure error that says "Microsoft SQL: Could not find stored procedure 'SQL'."

I'm confused. What do I need to do to get these distinct values in? Do I need a stored procedure? If so, does anyone have any other workarounds for this?

BA_Pete · ‎02-27-2023

Hi @rwlsxn ,

Exactly what type of SQL DB is your source? SQL Server, Oracle etc.?

Most SQL DB sources will actually fold back to the source from Power Query when done correctly so you shouldn't really need to write any actual SQL code to get this working quickly.

https://learn.microsoft.com/en-us/power-query/power-query-folding

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

View solution in original post