Using External SQL Files to Get Data with Power Query

I am a big fan of writing SQL with Power Query. I do a lot of exploration with SQL and oftentimes when making a Power BI Report with loosely-defined requirements (all of them lol), I follow this framework:

• Perform light SQL at the start, explore and mash up with Power Query
• As things make more sense, work to make Power Query fold as much as possible
• As things really take shape, look to either model at the source or write SQL that will cut out as many steps as possible but still will fold (as much as I can)

This has worked out really well for me. I don’t end up spending a lot of time writing and rewriting SQL based on hunches and can show the business line data that oftentimes I use the Power Query UI to transform. As things take shape, I can rework a little, get things to fold, and then pass steps upstream. Refresh times stay down, my development time is impactful, and I can make it look like I can walk and chew gum at the same time.

One of my favorite patterns that I learned from others (I think I learned this one from Ken Puls), is to use this pattern when using SQL with Power Query:

let
    Source = #Connection information,
    Query = #SQL Query string,
    Run = Value.NativeQuery(Source, Query, null,   
         [EnableFolding=true])
in
    Run

What this framework does is (1) connect to your database source, (2) add the SQL as a distinct step that you can see in Power Query applied steps, and (3) run the query against the source while keeping folding in place.

The part I like about it the most is the second step. This allows you to “show your work” in Power Query easily. Anyone reviewing your work can easily see the SQL used and move on, without having to open previous steps and possibly paste it somewhere else to reformat it.

Today I went back to this to try and extend it a little: How could you call a .sql file in that second step?

Use Case

At face value, this seems like an unnecessary step. But I have worked with folks, especially ones that use Python, that will write their SQL code separately and call the file within their Python script. In fact, I have done that as well with Python and personally like it.

So, how would you do it with M? It’s actually not that bad at all. All you have to do is connect to a text file and combine all of the rows. Here is a function that does that (NOTE: This example shows how to connect to a local file only, other use cases like OneDrive would have to be tailored accordingly):

let
   fnGetTextContents = 

(filepath as text)=>

let
    file = Table.FromColumns({Lines.FromBinary(File.Contents(filepath), null, null, 1252)}),
    return = Text.Combine(file[Column1], "#(lf)")
in
    return
in
    fnGetTextContents

Again, it is pretty straightforward. Connect and combine the file. Easy peasy. After that, connecting, getting the SQL, and running looks something like this:

let
	// Connect to source
    Source = Sql.Database("SEVER_NAME", "DATABASE_NAME"),
    
    // Get SQL Script
    Query = fnGetTextContents(sql_file),
    
    // Run it and fold
    Run = Value.NativeQuery(Source, Query, null, [EnableFolding=true])
in

    Run

I tried to get cute and expand this even further. What if you could pass in what is generated as the “Source” step when you make a connection and really just make this one function call? That would look something like this:

(Source as table, sql_file_path as text)=>

let
    fnGetSQLFile = (filepath as text)=>
    let
        File = Table.FromColumns({Lines.FromBinary(File.Contents(filepath), null, null, 1252)}),
        Return = Text.Combine(File[Column1], "#(lf)")
    in
        Return,
    Query = fnGetTextContents(sql_file_path),
    Run = Value.NativeQuery(Source, Query, null, [EnableFolding=true])
in
    Run

To call this last one, you would do something like this:

However, I noticed that I could not see if folding was occurring in Power BI Desktop:

Closing Thoughts

When you are collaborating with others or just want to be able to alter the base SQL tied to a dataflow or report without having to republish things, something like this may make some sense. I personally like doing a lot of work (specifically SQL work) in VS Code, so if I can stay there as long as I can before having to dip in to Power BI for things like this, I will probably be more proficient in the long run.