How to Decrypt Data in Power BI

Working with sensitive data in Power BI requires a careful balance between analysis and security. While Power BI has robust features for securing your report itself, you often need to analyze data that's already encrypted at the source, like customer emails or personal health information. This article will guide you through the methods for decrypting data directly within Power BI, empowering you to build secure and insightful reports.

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 Decrypt Data Inside Power BI?

You might wonder why you wouldn't just decrypt the data before loading it. While that's an option, decrypting within your Power BI workflow offers significant advantages, especially regarding security and compliance.

Consider regulations like GDPR, HIPAA, or CCPA, which impose strict rules on handling Personally Identifiable Information (PII). By keeping data encrypted in your source database (like a SQL Server or data warehouse), you minimize your security exposure. The data remains protected at its source, and decryption only occurs within the secure environment of Power BI's data model right before it's used for analysis. This minimizes the number of insecure "copies" of sensitive information floating around in staging files or intermediate databases.

Common Scenarios for In-Tool Decryption:

• Marketing Analytics: Analyzing customer behavior tied to specific email addresses or names without exposing that raw PII in the source database.
• Financial Reporting: Working with encrypted transaction details, account numbers, or salary information.
• Healthcare Data: Analyzing patient outcomes while ensuring patient IDs and sensitive health records are handled securely according to HIPAA standards.
• Human Resources: Creating dashboards on employee performance or compensation where sensitive info like salaries or social security numbers are encrypted at the source.

Decripting inside Power BI gives you granular control. The raw, sensitive data is never visible to someone who only has access to the source database. The decrypted values exist only within your managed Power BI dataset, which itself has layers of security and access control.

Understanding the Core Method: Power Query (M Language)

Power BI doesn't have a simple, one-click "Decrypt Column" button. The primary way to handle this is through the Power Query Editor, the powerful data transformation engine that works behind the scenes. Using its scripting language, called M, you can create custom functions to perform nearly any text or data transformation - including decryption.

The general process involves three key steps:

1. Connect to your source data that contains encrypted columns.
2. Create a custom M function that contains your specific decryption logic (e.g., using an algorithm like AES).
3. Invoke (or call) that function for each row of your encrypted column to generate a new column with the decrypted values.

It's important to distinguish this from Power BI's built-in encryption features. Power BI can encrypt the PBIX file itself and the dataset when it's published to the service (encryption at rest). What we're talking about is different: decrypting the individual data values within a column so they can be processed and visualized.

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-by-Step Guide: Decrypting Data Using a Custom Function

Let's walk through a realistic, though simplified, scenario. Imagine you have a table from a CRM with a column of email addresses encrypted using the AES algorithm and a secret key. Our goal is to decrypt this column in Power BI to use the emails for analysis.

Disclaimer: Real-world encryption implementations, especially AES, can be complex to write from scratch in M language. Some cryptographic libraries might not be available. For demonstration, we'll outline the exact structure. For highly complex cases, using an external helper like an Azure Function is often a more scalable approach (which we'll touch on later).

Step 1: Get Your Data into Power BI

First, connect to your data source. This could be a SQL Server database, an Excel file, a SharePoint list, or any other source where your encrypted data lives.

1. In Power BI Desktop, go to the Home ribbon and click Get Data.
2. Select your data source (e.g., SQL Server). Enter the server details and connect.
3. Choose the table containing your encrypted data from the Navigator and click Transform Data. This will open the Power Query Editor.

You should now see your table in Power Query, with a column full of seemingly random strings of encrypted text.

Step 2: Create a Parameter for Your Secret Key

You should never hardcode your secret encryption key directly into a query. It's a major security risk. A better practice is to store it in a Power Query parameter. This separates the logic from the secret key itself.

1. In the Power Query Editor, go to the Home ribbon and click Manage Parameters → New Parameter.
2. Set it up as follows:
3. Click OK. You now have a secure place to manage your key.

For even higher security, this key could be retrieved from Azure Key Vault, but using a parameter is a great first step.

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 3: Create the Custom Decryption Function

This is where the magic happens. We will create a new, blank query and turn it into a reusable function that performs decryption.

1. In the query pane on the left, right-click and select New Query → Blank Query.
2. A new query will appear, probably named Query1. Right-click it and rename it to something meaningful, like fx_DecryptAES.
3. With the new query selected, go to the Home ribbon and click Advanced Editor.
4. Delete any existing code and paste in the M script for your decryption function. A conceptual AES decryption function stub might look like this:

(encryptedText as text) as text =>
let
    // Note: The below functions for AES decryption are conceptual placeholders.
    // Real AES decryption in pure M is complex. This structure shows HOW you'd implement it
    // if you had the cryptographic functions available or were calling an external script.

    // 1. Convert the Hex/Base64 input to binary
    binaryData = Binary.FromText(encryptedText, BinaryEncoding.Base64),

    // 2. Call the (conceptual) decryption function
    decryptedBinary = Crypto.Decrypt(binaryData, SecretKey), // 'Crypto.Decrypt' is a placeholder

    // 3. Convert the resulting binary back to readable text
    resultText = Text.FromBinary(decryptedBinary, TextEncoding.Utf8)
in
    resultText

Important: The Crypto.Decrypt function in the example above is purely for illustration. Power Query M doesn't have a native library for cryptographic algorithms like AES. If you need true AES decryption, your function would typically connect to an external tool (like an Azure Function or a Python script) that can handle the complex calculations. However, the structure of creating and invoking the function remains the same.

Step 4: Invoke the Custom Function to Create a New Decrypted Column

Now that you have your decryption function ready, a secure parameter for your key, and your table loaded, let's put it all together.

1. Select your main data query (the table with the encrypted emails).
2. Navigate to the Add Column ribbon tab.
3. Click on Invoke Custom Function.
4. In the dialog box:
5. Click OK.

Power Query will now execute your custom function for every single row in your table. It will take the encrypted value from your specified column, run it through your decryption logic, and place the result in the new Decrypted Email column.

Step 5: Final Cleanup

Your table now has both the original encrypted column and the new decrypted one. For security and clarity, it's best to remove the original encrypted column if you don't need it for any other purpose in the model.

1. Right-click the header of the original encrypted column.
2. Select Remove.
3. Finally, click File → Close & Apply in the upper-left corner to load your transformed data into the Power BI model.

You can now use your newly decrypted column in visuals, measures, and reports just like any other field!

Performance and Security Considerations

While powerful, this row-by-row decryption approach has implications you should be aware of.

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

Performance

Invoking a custom function for every row can be resource-intensive and may slow down your data refresh times, especially with large datasets (hundreds of thousands or millions of rows). This operation prevents 'query folding' — the process where Power Query translates your steps into a single, efficient query for the source system (like SQL).

• For small to medium datasets (up to ~100k rows): The performance impact is usually acceptable.
• For large datasets (millions of rows): It's often more performant to handle decryption at the database level if possible. You could create a secure database view in SQL that performs the decryption and have Power BI connect to that view. The tradeoff is that decrypted data now exists at the database layer, which requires careful permission management.

Security: Protecting Your Key

Your decryption is only as secure as your key. As mentioned, never hardcode a key. Using parameters is good, but for enterprise-level security, consider sourcing the key from a secure vault.

• Azure Key Vault: The gold standard. Store your key in Azure Key Vault. Your Power BI dataflow or a custom connector can then authenticate and retrieve the key at runtime. This keeps the key entirely out of the PBIX file and Power Query Editor interface. It's an advanced technique but offers the highest level of security.

Alternative: Using Azure Functions for Decryption

If you find that the decryption logic is too complex for M language or you want to centralize the logic for reuse across multiple tools, a web service like an Azure Function is an excellent solution.

Here’s the high-level workflow:

1. Create an Azure Function: Write a simple HTTP-triggered function in a language like C#, Python, or PowerShell. This function accepts an encrypted string as input and returns the decrypted text. The secret key is stored securely within the Azure Function's application settings.
2. Connect from Power Query: In Power Query, instead of creating a custom M function, you use the Web data connector to call your Azure Function.
3. Pass Data to the Function: You would use a similar process to 'Invoke Custom Function', but you would be passing the values from your encrypted column as a parameter in the URL or body of the web request to your Azure Function.

This approach moves the heavy lifting of decryption to a scalable, secure, external service, keeping your Power BI file focused purely on data transformation and modeling.

Final Thoughts

Mastering data decryption directly in Power BI is a powerful skill. It allows you to maintain end-to-end security, keeping sensitive data encrypted at its source while still enabling deep and flexible analysis. By using Power Query custom functions and properly managing your secret keys, you can build secure reports that are compliant with modern data protection standards.

Of course, securely connecting all your data sources and building these models from scratch can be time-consuming, especially when you’re dealing with different platforms. At Graphed, we've automated this process. By connecting your tools to our platform, you skip much of the manual transformation work. Just ask for the dashboard you need in plain English — like "Show me a chart of sales by region from our Salesforce data" — and a live, updating dashboard is built instantly. It gives you the power of analysis without the hours spent in configuration. You can learn more and get started for free by visiting Graphed.