Nifty PowerShell Script For Power BI

Now that I’m working with Power BI more, I’m always creating and editing semantic models.

All of my data comes from a relational database, which means that I write a lot of SQL queries to load data into Power BI. Creating the tables is easy. I specify the database server, enter my database name, and paste the SQL query into Power BI. Child’s play.

But editing tables in semantic models annoys me.

After creating the table, you can use Power Query to modify your server, database, or query. The issue is that Power BI reformats the query into one long string. It replaces line breaks with a special character, #(lf).

It annoys me because when I copy and paste the query back into my RDBMS, I have to manually remove the special line break character and reformat my queries. I do this not only because I want the queries to be easier to read, but because the SQL won’t execute with #(lf) included.

I’m lazy. I don’t want to spend the 1-2 minutes doing this.

So, instead, I created a nifty PowerShell script to do it for me. Gotta love automation.

The script is simple and looks like this:

# This function cleans Power BI SQL query strings that contain #(lf).
# It allows us to make queries executable in SQL Server.

# First, read input from clipboard (ensure you copy the string before executing function)
$input = Get-Clipboard

# Now, replace #(lf) with actual line breaks
$output = $input -replace '#\(lf\)', "`r`n"

# Copy new string back to clipboard
Set-Clipboard -Value $output

Write-Output "I replaced your query with actual line breaks and saved it to your clipboard."
Write-Output "You're welcome."

The nice thing about this is that I don’t have to paste the query into my PowerShell terminal. So long as I copy it in Power BI, the PowerShell function makes the changes directly in my clipboard.

Sometimes, automating tedious tasks is as simple as that.