How to Run PowerShell Script in Power BI

Running PowerShell scripts inside Power BI can feel like unlocking a secret level in your data analysis game. It allows you to grab data from systems, APIs, and local files that Power BI can't otherwise reach, all while automating complex data cleanup before it even hits your report. This guide will walk you through exactly how to set up and run PowerShell scripts as a data source, turning you into a data automation pro.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use PowerShell with Power BI?

While Power BI has hundreds of native connectors, sometimes you need to get data from a source that isn’t on the list or transform it in a way that’s clunky with the standard Power Query tools. That’s where PowerShell comes in. It’s a powerful scripting language built into Windows that excels at automation and data manipulation.

Here are a few practical reasons to use PowerShell scripts in your Power BI workflow:

• Accessing Custom APIs: While Power BI can connect to REST APIs, PowerShell's Invoke-RestMethod cmdlet often simplifies the process, especially for APIs requiring complex authentication or headers that are tricky to configure in Power Query.
• Interacting with Local Systems: Need to report on the files in a local folder, check the status of a Windows service, or pull information from Active Directory? PowerShell can access this system-level information and feed it directly into your reports. For example, you could create a dashboard monitoring available disk space on a set of servers.
• Pre-Processing Data: Sometimes data needs heavy-duty cleaning before you can work with it. You can write a PowerShell script to parse messy log files, restructure weirdly formatted text files, or perform calculations and transformations that would be hard to replicate with Power Query’s M language.
• Automating Repetitive Tasks: If part of your reporting process involves logging into a system without an API, downloading a file, renaming it, and then loading it into Power BI, you can often automate the entire download and formatting step with a single PowerShell script.

In short, PowerShell acts as a bridge, letting you pull in almost any data you can access programmatically and pipe it cleanly into Power BI for analysis.

Prerequisites: Getting Your System Ready

Before you dive in, you need to ensure two things are properly configured on your machine. All modern versions of Windows come with PowerShell pre-installed, so you just need to check your settings.

1. Check Your PowerShell Version

While most scripts will work with the default version of PowerShell in Windows 10 and 11, it's good practice to know what you're running. Open a PowerShell terminal (just search for "PowerShell" in the Start Menu) and type the following command:

$PSVersionTable

This command will display a table of information, including the PSVersion. Generally, any version 5.1 or higher is perfect for this purpose.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Configure the Execution Policy

By default, Windows restricts scripts from running to protect you from malicious code. This is the single most common reason why a script fails when running it from Power BI. To fix this, you need to change the "Execution Policy."

First, check your current policy by running:

Get-ExecutionPolicy

If it says Restricted, you won't be able to run scripts. To allow scripts you've written yourself to run, you should change the policy to RemoteSigned. It's a good compromise on security, as it prevents unsigned scripts downloaded from the internet from running. Use the following command:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

Using -Scope CurrentUser is important because it applies the change only to your user account and doesn't require administrator permissions. After running this command, you're ready to connect to Power BI.

Step-by-Step Guide: Your First PowerShell Data Source

Let's start with a simple but practical example: creating a report of all the files in a specific folder on your computer. This is a great way to monitor a downloads folder or a shared drive location.

Step 1: Write the PowerShell Script

The key to a successful script is ensuring its output is structured like a table. Power BI understands objects with properties, so cmdlets that produce lists of objects work best. The Get-ChildItem cmdlet is perfect for our example.

Create a script like this. Just change "C:\Your\Folder\Path" to a real folder path on your machine.

Get-ChildItem -Path "C:\Your\Folder\Path" -Recurse | Select-Object Name, DirectoryName, Length, CreationTime, LastWriteTime

Let's break down this one-liner:

• Get-ChildItem -Path "C:\Your\Folder\Path" -Recurse: This gets a list of all files and folders inside the specified path and all its subfolders.
• |: This is the "pipe" character. It takes the output of the first command and "pipes" it as input to the next command.
• Select-Object Name, DirectoryName, Length, CreationTime, LastWriteTime: This tells PowerShell to only keep the specified properties for each file. These properties will become the columns in our Power BI table.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Connect Power BI to the Script

Now, open Power BI Desktop and let's pull this data in.

1. From the Home tab, click Get Data.
2. In the Get Data window, choose Other from the left-hand menu and then select PowerShell script.
3. Click Connect.

A dialog box will appear. Paste your one-line PowerShell script directly into the box and click OK.

Power BI will now execute the script in the background. If you get a privacy warning about running a native database query, just click Run.

Step 3: Transform Data in the Power Query Editor

After the script runs successfully, a Navigator window will appear, showing you the data that PowerShell returned. You will see a single table named "Output" representing the script's results.

Select the "Output" table and click Transform Data. This will open the Power Query Editor, where you can see your data neatly arranged in columns: Name, DirectoryName, Length, etc.

From here, you can treat it like any other data source. You can:

• Change the data types (e.g., ensure Length is a whole number and CreationTime is a DateTime type).
• Rename the columns to be more user-friendly (e.g., "File Size" instead of "Length").
• Add calculated columns, like converting the file size from bytes to megabytes.

Step 4: Load and Visualize

Once you're happy with the data format, click Close & Apply on the Home tab. The data will be loaded into your Power BI data model.

Now you can build visuals from it! For instance, you could create a bar chart showing the number of files by directory or a table visual that lists all files larger than a certain size. You've successfully used a PowerShell script as a dynamic data source.

Advanced Example: Grabbing Data from a REST API

Querying local files is useful, but the real power comes from using PowerShell to fetch data from web services. Let’s pull some data from the JSONPlaceholder API, a free public API for testing and prototyping.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: The API Script

We'll use the Invoke-RestMethod cmdlet to make an HTTP GET request to an API endpoint. This cmdlet is smart enough to automatically parse the JSON response into PowerShell objects.

Here's the script:

Invoke-RestMethod -Uri "https://jsonplaceholder.typicode.com/posts"

That's it! This command requests a list of sample blog posts from the API and PowerShell automatically structures the response.

Step 2: Connect and Transform in Power BI

The process is nearly identical to our first example:

1. In Power BI, open Get Data > Other > PowerShell script.
2. Paste the Invoke-RestMethod command into the script box and click OK.
3. Power BI will run the query. When the Navigator appears, select the resulting table and click Transform Data.
4. The Power Query Editor will open, showing you the columns retrieved from the API: userId, id, title, and body.
5. Finalize your data types, rename columns if needed, and click Close & Apply.

You have now pulled live data from a web API into Power BI using just a simple line of PowerShell script.

Important Considerations and Limitations

While powerful, there are a few things to keep in mind when using PowerShell scripts in your reports.

• Scheduled Refresh in Power BI Service: This is the biggest hurdle. A Power BI report that uses a PowerShell script cannot be refreshed in the Power BI Service (app.powerbi.com) by default. The script runs on your local machine, and the cloud service has no access to it. To enable scheduled refreshes, you must install and configure a Personal Mode Power BI Gateway on the same computer where the script runs. The gateway acts as a secure bridge, allowing the Power BI Service to request that your machine re-run the script and send the updated data.
• Error Handling: If your script fails for any reason (e.g., the API is down, a file path is wrong), a Power BI refresh will fail. For production reports, it's wise to build error handling into your PowerShell scripts using try...catch blocks to make them more robust.
• Credentials and Security: Never hardcode sensitive information like usernames, passwords, or API keys directly in your script. Look into PowerShell's modules for managing secrets, like Microsoft.PowerShell.SecretManagement, for a more secure approach.

Final Thoughts

Learning how to run PowerShell scripts in Power BI dramatically expands your data gathering capabilities. It provides a flexible way to automate data collection from local systems, file folders, and APIs that don’t have native Power BI connectors, giving you the power to report on nearly anything.

The goal is always to get insights faster without the friction of manual data wrangling. For many standard marketing and sales data sources like HubSpot, Salesforce, or Google Analytics, scripting isn't necessary. We built Graphed to create a seamless data pipeline automatically. Instead of writing code, you just connect your platforms in a few clicks, and our AI builds real-time dashboards for you based on plain-English requests, helping you get answers in seconds instead of hours.