How to Mask Data in Power BI

Masking sensitive data in your Power BI reports is essential for keeping private information secure. It allows you to share valuable insights with your team or clients without exposing details like personal names, email addresses, or financial data. This article will walk you through the most effective methods to mask data in Power BI using Power Query and DAX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is Data Masking and Why Is It Necessary?

Data masking, also known as data obfuscation, is the process of hiding original data with modified content. The goal is to protect sensitive information while still having a usable dataset for analysis, development, or testing. For example, you might replace a real customer's email address, jane.doe@email.com, with a generic, masked version like j***.***@email.com.

In a business context, this is crucial for several reasons:

• Regulatory Compliance: Regulations like GDPR, HIPAA, and CCPA have strict rules about handling personally identifiable information (PII). Proper data masking helps you stay compliant and avoid hefty fines.
• Internal Security: Not everyone in your organization needs access to every piece of raw data. A sales analyst might need to see sales figures by region but shouldn't see individual customer contact details.
• Sharing Reports Confidently: When sharing dashboards with external stakeholders or a wide internal audience, masking ensures you're only showing them what they need to see.

Method 1: Static Data Masking with Power Query

Static masking is done in the Power Query Editor during the data transformation stage. This method applies the same mask for everyone who views the report. It's an excellent choice for straightforward scenarios where you want to permanently hide or alter sensitive columns before the data even loads into your Power BI data model.

Here’s how you can implement static masking in Power Query.

Step-by-Step Guide to Power Query Masking

Let's imagine you have a customer table with names, email addresses, and phone numbers you need to protect.

1. Open Power Query Editor: In Power BI Desktop, go to the Home tab and click on Transform data. This will open the Power Query Editor.
2. Select Your Table: Choose the query (table) in the left-hand pane that contains the sensitive data you wish to mask.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Technique A: Replacing the Entire Value

The simplest method is to replace the entire column's content with a static placeholder like "[REDACTED]" or "Confidential".

• Select the column you want to mask (e.g., CustomerName).
• Go to the Transform tab.
• Click Replace Values. Since you want to replace everything, this isn't the most direct tool. Instead, it's easier to remove the column and add a new one.
• A better way is to select the column, go to the Add Column tab, and click Custom Column.
• In the dialog box, name your new column (e.g., Masked Name) and simply enter a text string in the formula box, like " [Confidential]" . Then, you can remove the original CustomerName column.

Technique B: Partial Masking Using a Custom Column

Often, you'll want to show a part of the data for context while masking the rest. A custom column is perfect for this.

Example: Masking an Email Address

Let's mask jane.doe@email.com to look like jan***@email.com.

• With your query selected, go to the Add Column tab and click Custom Column.
• Name the new column something like MaskedEmail.
• Enter the following M formula. This code finds the "@" symbol, takes the first 3 characters of the username, and combines it with a masked version of the domain.

let
    Email = [EmailColumn],
    Split = Splitter.SplitTextByDelimiter("@")(Email),
    Username = Split{0},
    Domain = Split{1},
    MaskedUsername = Text.Start(Username, 3) & Text.Repeat("*", Text.Length(Username)-3),
    FinalEmail = MaskedUsername & "@" & Domain
in
    FinalEmail

Note: Replace [EmailColumn] with the actual name of your email column. You might need to adjust the number of characters you show (e.g., change 3 to 2).

Example: Masking a Phone Number

Let's turn 555-123-4567 into XXX-XXX-4567.

• Click Custom Column again. Name it MaskedPhone.
• Enter this formula, which replaces the first part of the string with "X"s but keeps the last 4 digits.

"XXX-XXX-" & Text.End([PhoneNumberColumn], 4)

Replace [PhoneNumberColumn] with the name of your phone number column.

1. Clean Up: After creating your new masked columns, right-click the original sensitive columns (like EmailColumn, PhoneNumberColumn) and select Remove.
2. Apply Changes: Once you're done, click Close & Apply on the Home tab to load the changes into your data model.

• Pros: Simple to implement, removes sensitive data early in the process. Great for development or creating anonymized datasets.
• Cons: It's static. You can't show the original data to any user, as it's been permanently replaced during the data load.

Method 2: Dynamic Data Masking with DAX

Dynamic masking is a more advanced and flexible approach. It allows you to display data differently based on which user is viewing the report. An admin might see the full, unmasked data, while a general user sees the masked version. This is achieved using Data Analysis Expressions (DAX) combined with Row-Level Security (RLS) roles.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to Dynamic DAX Masking

This method doesn't alter the underlying data. Instead, it creates a DAX measure that conditionally decides what to display at the report level.

1. Create User Roles

First, you need to define who gets to see what. You do this by creating roles in Power BI Desktop.

• Go to the Modeling tab in the ribbon.
• Click Manage roles.
• In the dialog box, click Create to make your first role. Let's call it Admin Viewer. This role will see all the data, so we won't apply any filters to it.
• Click Create again to make a second role. Let's name it General Viewer. This is the role that will see the masked data. We also won't define a DAX filter here, the logic will be handled in a measure.
• Click Save.

2. Create the DAX Measure for Masking

Now, instead of putting the raw data column directly in your visuals, you'll create a DAX measure that contains the masking logic.

• Go to the Report View or Data View.
• On the Home tab, click New measure.
• Let's create a measure to dynamically mask customer names. In the formula bar, enter the following DAX formula:

Masked Customer Name = 
VAR IsAdmin = (USERPRINCIPALNAME() = "admin_user@yourcompany.com")
RETURN
IF(
    IsAdmin,
    SELECTEDVALUE('Customers'[CustomerName]),
    "Confidential"
)

Let's break this down:

• USERPRINCIPALNAME() is a DAX function that returns the email address of the user currently viewing the report (e.g., john.smith@yourcompany.com). In Power BI Desktop, it returns your Windows username.
• We check if the logged-in user's email matches a specific admin email.
• IF the user is an admin (IsAdmin is true), the measure returns the actual value from the CustomerName column using SELECTEDVALUE().
• ELSE, it returns the string "Confidential".

For more scalable role management, you can create a separate table with user emails and their roles, then use a DAX function like LOOKUPVALUE to check the user's role instead of hardcoding emails into the measure.

3. Use the Measure in Your Visuals

Now, update your visuals. Replace the field used for customer names with the new [Masked Customer Name] measure.

The visuals will now display either the real name or "Confidential" based on who is viewing the report.

4. Test Your Roles

It's essential to test that your roles work as expected before publishing.

• On the Modeling tab, click View as.
• Check the box for General Viewer and click OK. The report will refresh, and all instances of your measure should show the masked version.
• Then, check View as with Admin Viewer (or "None") to see the unmasked data.

5. Assign Users in Power BI Service

After publishing your report to the Power BI Service, assign your colleagues to the roles you created. Use the dataset security settings to add users' email addresses to the "Admin Viewer" and "General Viewer" roles.

• Pros: Highly flexible, secure, and enables different views for different users from the same report.
• Cons: Requires more setup (DAX, roles), and complex measures can potentially impact report performance.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

A Note on Object-Level Security (OLS)

For an even higher level of security, you can use Object-Level Security (OLS). Instead of just masking the data within a column, OLS allows you to hide an entire column or even a full table from a specific user role.

OLS is typically configured using third-party tools like Tabular Editor. A user in a role with an OLS rule applied won't just see masked data — they won't even know the column exists. This can be useful for preventing users from trying to reverse-engineer data and for simplifying the model for different user groups.

Final Thoughts

Securing sensitive data is non-negotiable, and Power BI provides robust tools to get the job done. For simple, report-wide anonymization, static masking in Power Query is a quick and effective solution. For a more sophisticated, user-based approach, combining DAX measures with security roles offers a dynamic and flexible way to control exactly who sees what.

For teams that need answers from their data without the steep learning curve of tools like Power BI, setting up this level of security can feel overwhelming. At Graphed, we’ve simplified the entire process. You can securely connect data sources in a few clicks and a single dashboard, and then simply describe the report or chart you want in plain English. We handle the complexity of data modeling and visualization in the background, giving you and your team fast, secure insights without weeks of training.