Picture this: your organization has a single sales dataset covering every region, every product line, and every customer tier. The regional managers need to see their own territory's data. The finance team needs cost figures that salespeople absolutely should not see. The executive team wants everything. And the data team — you — is supposed to make all of this happen without maintaining seventeen separate reports or a sprawling mess of duplicated queries. Oh, and the permission structure changes every time someone gets promoted, reassigned, or hired.
This is the reality of enterprise data work, and it's exactly the kind of problem that Power Query M is underequipped to solve out of the box — but surprisingly capable of solving when you know how to architect it properly. Row-level security in Power BI proper lives in DAX and the Analysis Services engine. But there's an entire class of problems where you need dynamic data filtering before the data hits the model: during ETL, in paginated reports, in Excel-based workbooks with shared connections, or in scenarios where you're staging data for downstream consumers who shouldn't ever see certain rows in the first place. Power Query M is exactly where that work happens, and doing it well requires understanding the language at an architectural level.
By the end of this lesson, you will have built a complete, production-grade filtering pipeline in Power Query M that reads from a permission table, resolves the current user's access profile, and applies multi-dimensional row-level filters to a dataset — all driven by parameters that make the system maintainable and testable. You'll understand the security trade-offs involved, how to avoid the pitfalls that make naïve implementations either too permissive or too brittle, and how to structure your M code so another developer can actually understand what's happening six months from now.
What you'll learn:
let expressions to create testable, parameterized filter pipelinesTable.SelectRows with runtime-resolved predicatesThis lesson assumes you are comfortable with the following:
let...in expressions in M and understanding lazy evaluationTable.SelectRows, Table.Join, and Table.Group without the Query Editor GUIIf you're shaky on any of those, the Intermediate M Language path on this platform covers them directly.
Before you write a single line of M, you need to understand why this problem is architecturally interesting — because the naive solution will bite you.
The naive approach most people reach for is something like: "I'll just add a filter step that checks a hardcoded username against a hardcoded list." That works exactly once, for one person, until any of the following happen: someone new joins, someone's role changes, you add a new data dimension, or you need to audit who saw what. In short, it works in a demo and fails in production.
The correct architecture separates three concerns:
1. Identity Resolution — Determining who the current user is. In Power BI service, this is UserPrincipalName() from DAX, but in Power Query M, you typically receive this as an input parameter. This is the seam where your system interfaces with whatever authentication infrastructure your organization uses.
2. Permission Resolution — Given an identity, what is that person allowed to see? This is encoded in a permission table — a structured dataset that maps users (or roles) to access rules. The permission table is the heart of the system.
3. Data Filtering — Applying the resolved permissions to the actual data. This is where your M code does the heavy lifting.
Keeping these three layers cleanly separated means you can change any one of them without breaking the others. You can swap out your permission table source (from a SharePoint list to a SQL table to a hardcoded development table) without touching your filtering logic. You can change your filtering logic without touching how you resolve identities.
Let's build this out properly.
The permission table is the most important design decision in this entire system. Get it wrong and you'll either rewrite it from scratch or maintain a fragile hack forever.
For our scenario, we're working with a sales transactions dataset. Each row represents a sale with the following relevant columns: Region, ProductLine, CustomerTier, SalesRepID, and TransactionAmount. Our access rules need to support:
TransactionAmount (which others see as masked)SalesRepID)Here's the permission table schema we're going to build around:
| Column | Type | Description |
|---|---|---|
UserEmail |
Text | The user's identity (email address) |
RoleName |
Text | A logical role name for grouping |
FilterDimension |
Text | Which column the rule applies to (Region, ProductLine, SalesRepID, or ALL) |
FilterValue |
Text | The allowed value for that dimension, or * for wildcard |
CanSeeAmount |
Logical | Whether this role can see transaction amounts |
Priority |
Number | When rules conflict, higher priority wins |
A sample permission table might look like this:
| UserEmail | RoleName | FilterDimension | FilterValue | CanSeeAmount | Priority |
|---|---|---|---|---|---|
| sarah.chen@company.com | RegionalManager | Region | Northeast | false | 10 |
| sarah.chen@company.com | RegionalManager | Region | Midwest | false | 10 |
| david.okafor@company.com | ProductOwner | ProductLine | Enterprise | false | 10 |
| finance@company.com | Finance | ALL | * | true | 20 |
| exec@company.com | Executive | ALL | * | true | 30 |
| james.wu@company.com | SalesRep | SalesRepID | SR-0042 | false | 5 |
Notice several deliberate design decisions here:
One user can have multiple rows — Sarah Chen has two rows because she manages two regions. This is the right choice. Trying to cram multiple values into a single cell (comma-separated lists, JSON blobs) creates a parsing nightmare downstream.
The FilterDimension = "ALL" with FilterValue = "*" is the wildcard pattern. It means "no filter on this dimension." We'll handle this explicitly in our M code so it's not ambiguous.
CanSeeAmount is a boolean column-level permission. This is simpler than a full column permission system but illustrates the pattern — you'd extend it for more columns as needed.
Priority handles conflict resolution. If a user somehow appears in both a RegionalManager role and a Finance role (perhaps through a role group expansion you do upstream), the higher priority rule wins.
Important design note: This schema assumes you've already resolved group memberships upstream. If your organization uses Active Directory groups or Azure AD groups, you'll want a pre-processing step that expands group membership into individual user rows before this permission table is consumed. Doing that expansion inside Power Query M is possible but adds significant complexity and latency — it's better done in your data pipeline or a separate query that refreshes on a slower schedule.
Power Query parameters are the bridge between your filtering pipeline and the outside world. We need two of them:
CurrentUserEmail — A text parameter that holds the email address of the user whose permissions we're resolving. In a Power BI report, this would be populated at runtime. In a development context, you'll set it to your own email to test your own access, or to a test user's email to validate their permissions.
PermissionTableSource — A text parameter that tells the pipeline where to load the permission table from. This lets you point at a development permission table during testing and a production one on deployment without changing any query logic.
To create these in Power BI Desktop: open the Power Query Editor, go to the Home ribbon, click Manage Parameters, then New Parameter. For CurrentUserEmail, set the type to Text, mark it as Required, and set a current value of your own email address for development. For PermissionTableSource, set it to the path or connection string of your permission data source.
Security warning: Parameters in Power Query M are not a security enforcement mechanism by themselves. They're values that flow into your query logic. In Power BI service, a user can potentially see the M query definition, which means they could see the parameter value. Never put secrets in parameters. The filtering this pipeline does is appropriate for data staging and ETL scenarios, or as a complement to model-level RLS — not as a standalone security boundary in a shared report.
Now let's build the actual queries.
We'll create a dedicated query called ResolvedPermissions that takes the CurrentUserEmail parameter and returns a clean permission record for that user. This query does no filtering of source data — it only deals with the permission table. This separation matters enormously for testing and debugging.
let
// Load the raw permission table
// In production, this might be a SQL table, SharePoint list, or an Excel file
RawPermissions =
Excel.Workbook(
File.Contents("C:\DataPipeline\permissions.xlsx"),
true,
true
){[Item="Permissions", Kind="Sheet"]}[Data],
// Ensure correct types - never trust raw source types
TypedPermissions = Table.TransformColumnTypes(
RawPermissions,
{
{"UserEmail", type text},
{"RoleName", type text},
{"FilterDimension", type text},
{"FilterValue", type text},
{"CanSeeAmount", type logical},
{"Priority", Int64.Type}
}
),
// Filter to only the current user's rows
// CurrentUserEmail is a Power Query parameter
UserRows = Table.SelectRows(
TypedPermissions,
each Text.Lower([UserEmail]) = Text.Lower(CurrentUserEmail)
),
// If the user has NO rows in the permission table,
// we return an empty permission set — deny by default
HasPermissions = Table.RowCount(UserRows) > 0,
// Find the maximum priority across all this user's rows
// We'll use this to resolve conflicts
MaxPriority =
if HasPermissions
then List.Max(Table.Column(UserRows, "Priority"))
else 0,
// Determine if this user has any wildcard (ALL/*) rule at max priority
// A wildcard rule means "see everything on this dimension"
WildcardRows = Table.SelectRows(
UserRows,
each [FilterDimension] = "ALL" and [FilterValue] = "*"
and [Priority] = MaxPriority
),
IsFullAccess = Table.RowCount(WildcardRows) > 0,
// Determine column-level permissions
// If ANY of the user's max-priority rows grants CanSeeAmount, they can see it
// (More permissive wins for column access — adjust this logic for your policy)
CanSeeTransactionAmount =
if HasPermissions
then List.AnyTrue(
Table.Column(
Table.SelectRows(UserRows, each [Priority] = MaxPriority),
"CanSeeAmount"
)
)
else false,
// Build dimension-specific filter lists
// Group the user's rows by FilterDimension to get lists of allowed values
// Only include rows at max priority, and exclude wildcard dimensions
DimensionRows = Table.SelectRows(
UserRows,
each [Priority] = MaxPriority and [FilterDimension] <> "ALL"
),
// Group by dimension to get allowed values per dimension
DimensionGroups = Table.Group(
DimensionRows,
{"FilterDimension"},
{
{
"AllowedValues",
each Table.Column(_, "FilterValue"),
type list
}
}
),
// Convert the grouped table into a record for easy lookup
// e.g., [Region = {"Northeast", "Midwest"}, ProductLine = {"Enterprise"}]
DimensionRecord = Record.FromList(
Table.Column(DimensionGroups, "AllowedValues"),
Table.Column(DimensionGroups, "FilterDimension")
),
// Assemble the final permission record
PermissionProfile = [
UserEmail = CurrentUserEmail,
HasAccess = HasPermissions,
IsFullAccess = IsFullAccess,
CanSeeTransactionAmount = CanSeeTransactionAmount,
DimensionFilters = DimensionRecord,
AppliedPriority = MaxPriority
]
in
PermissionProfile
This query returns a single record — not a table. That record is the user's complete permission profile. Let's walk through what's happening in the key steps.
The DimensionGroups step is doing real work: it collapses all the individual permission rows into a grouped structure where each dimension maps to a list of allowed values. For Sarah Chen, this produces something like:
DimensionFilters = [
Region = {"Northeast", "Midwest"}
]
The Record.FromList call at the end converts that grouped table into a proper M record, which gives us fast, named lookup access in the filtering step. This is a common M pattern worth internalizing — Table.Group followed by Record.FromList is how you turn a key-value table into a navigable record structure.
Tip: The
Text.Lowercomparison inUserRowsis critical. Email addresses in Active Directory or Azure AD are sometimes stored with inconsistent casing. Normalizing both sides to lowercase before comparing saves you hours of debugging mysterious "user not found" situations.
Now we have a clean permission profile. The next query — let's call it FilteredSalesData — takes the raw sales data and applies the permission profile to it. This is where the actual row-level filtering happens.
let
// Load the raw sales data
RawSalesData =
Sql.Database(
"prod-sql-server.company.com",
"SalesDB",
[Query = "SELECT * FROM dbo.SalesTransactions"]
),
// Type the columns we'll be filtering on
TypedData = Table.TransformColumnTypes(
RawSalesData,
{
{"TransactionID", type text},
{"Region", type text},
{"ProductLine", type text},
{"CustomerTier", type text},
{"SalesRepID", type text},
{"TransactionAmount", type number},
{"TransactionDate", type date}
}
),
// Pull the permission profile — this references our ResolvedPermissions query
Permissions = ResolvedPermissions,
// If the user has no access at all, return an empty table
// This is the deny-by-default principle
AccessGate =
if not Permissions[HasAccess]
then Table.FirstN(TypedData, 0) // Empty table with correct schema
else TypedData,
// If the user has full access, skip dimension filtering entirely
// This is an important optimization — don't filter what doesn't need filtering
DimensionFilteredData =
if Permissions[IsFullAccess]
then AccessGate
else
let
// Get the dimension filter record
// e.g., [Region = {"Northeast", "Midwest"}, SalesRepID = {"SR-0042"}]
Filters = Permissions[DimensionFilters],
// Get the list of dimensions we need to filter on
FilterDimensions = Record.FieldNames(Filters),
// Apply filters for each dimension
// We use List.Accumulate to chain filter operations
// Each iteration narrows the dataset further (AND logic between dimensions)
FilteredResult = List.Accumulate(
FilterDimensions,
AccessGate,
(currentTable, dimension) =>
let
AllowedValues = Record.Field(Filters, dimension),
// Use List.Contains for the row predicate
// This handles single and multi-value allowed lists uniformly
Filtered = Table.SelectRows(
currentTable,
each List.Contains(
AllowedValues,
Record.Field(_, dimension)
)
)
in
Filtered
)
in
FilteredResult,
// Apply column-level security: mask TransactionAmount if not authorized
ColumnMaskedData =
if Permissions[CanSeeTransactionAmount]
then DimensionFilteredData
else Table.TransformColumns(
DimensionFilteredData,
{
{
"TransactionAmount",
each null, // Replace amount with null
type number
}
}
),
// Add an audit column so downstream consumers can see which profile was applied
// This is invaluable for debugging and compliance
AuditedData = Table.AddColumn(
ColumnMaskedData,
"_PermissionProfile",
each Permissions[UserEmail] & " | Priority:"
& Text.From(Permissions[AppliedPriority])
& " | FullAccess:"
& Text.From(Permissions[IsFullAccess]),
type text
)
in
AuditedData
The most interesting part here is the List.Accumulate call in DimensionFilteredData. This is worth understanding deeply because it's doing something that's not immediately obvious.
List.Accumulate is M's fold operation. It takes a list, a starting value (called the seed), and a function that takes the current accumulated result and the current list item and returns a new accumulated result. Here, we start with the full AccessGate table as our seed and iterate over the list of filter dimensions. For each dimension, we narrow the table further by applying a Table.SelectRows filter.
The result is that dimensions are combined with AND logic — a row must satisfy every dimension filter to survive. For Sarah Chen (who has a Region filter), only the Region dimension exists in her DimensionFilters record, so List.Accumulate only makes one pass. For a hypothetical user with both Region = Northeast and ProductLine = Enterprise, the accumulator runs twice, first filtering by Region, then filtering that result by ProductLine. The final dataset contains only rows in the Northeast that are also in the Enterprise product line.
Warning: The
List.Accumulatepattern withTable.SelectRowscreates nested query steps that Power Query evaluates lazily. In most cases this folds down to the data source as a single efficient query (query folding). However, if any intermediate step breaks folding — for example, if yourAllowedValueslist comes from a non-foldable source — the entire filter will execute in-memory. For large datasets, profile your query folding before deploying to production. You can check folding by right-clicking a step in the Applied Steps panel and looking for the "View Native Query" option.
The List.Contains approach already handles OR logic within a single dimension — Sarah Chen's Region filter is {"Northeast", "Midwest"}, and List.Contains returns true if the row's Region matches either value. But what happens when a user has access defined through multiple different roles?
Consider a user who is both a ProductOwner for Enterprise products AND a RegionalManager for the Southeast. They should see all transactions in the Southeast (any product) plus all Enterprise transactions (any region). That's OR logic across dimensions, not AND.
The schema design we've established doesn't handle this case well with a pure accumulator approach — the accumulator applies AND logic between dimensions, which would incorrectly restrict this user to only Southeast Enterprise transactions.
The solution is a pre-processing step in ResolvedPermissions that detects this situation and handles it differently. We'll add a flag for "mixed dimension types requiring union":
// In ResolvedPermissions, after building DimensionGroups...
// Check if the user has rules for more than one dimension type
// AND those dimension types could create an AND-logic problem
UniqueFilterDimensions = List.Distinct(
Table.Column(DimensionRows, "FilterDimension")
),
// If the user has multiple DIFFERENT filter dimensions (e.g., Region AND ProductLine)
// we need union semantics, not intersection semantics
// Flag this for the filtering pipeline
RequiresUnionFiltering = List.Count(UniqueFilterDimensions) > 1,
Then in FilteredSalesData, branch on this flag:
DimensionFilteredData =
if Permissions[IsFullAccess]
then AccessGate
else if Permissions[RequiresUnionFiltering]
then
// Union approach: apply each dimension filter separately and combine results
let
Filters = Permissions[DimensionFilters],
FilterDimensions = Record.FieldNames(Filters),
// Get a filtered table for each dimension independently
FilteredTables = List.Transform(
FilterDimensions,
(dimension) =>
Table.SelectRows(
AccessGate,
each List.Contains(
Record.Field(Filters, dimension),
Record.Field(_, dimension)
)
)
),
// Union all the filtered tables
// Table.Combine on identical schemas produces a UNION ALL
UnionResult = Table.Combine(FilteredTables),
// Remove duplicates that might appear if a row satisfies multiple filters
DeduplicatedResult = Table.Distinct(UnionResult, {"TransactionID"})
in
DeduplicatedResult
else
// Standard AND logic path (from before)
let
Filters = Permissions[DimensionFilters],
FilterDimensions = Record.FieldNames(Filters),
FilteredResult = List.Accumulate(
FilterDimensions,
AccessGate,
(currentTable, dimension) =>
Table.SelectRows(
currentTable,
each List.Contains(
Record.Field(Filters, dimension),
Record.Field(_, dimension)
)
)
)
in
FilteredResult,
The deduplication step using Table.Distinct on TransactionID ensures that a row satisfying multiple filter dimensions (a Northeast Enterprise transaction, in our example) doesn't appear twice in the result. This is a real-world edge case that will cause double-counting in aggregations if you don't handle it.
Architecture decision: Whether to use AND or OR semantics depends entirely on your business logic. The "what would make sense to the user" test helps here. A product owner who is also a regional manager probably expects to see Northeast + Enterprise (union), not just Northeast AND Enterprise (intersection). Document this decision explicitly in your permission table's companion documentation.
A filtering pipeline that you can only test by logging in as different users is a nightmare to maintain. We need a way to test the pipeline without changing the CurrentUserEmail parameter manually each time.
The solution is a dedicated test harness query. Create a new query called TestPermissionProfiles that validates the filtering logic against a set of known users:
let
// Define test cases: user email and expected behavior
TestCases = #table(
type table [
TestUserEmail = text,
ExpectedFullAccess = logical,
ExpectedCanSeeAmount = logical,
ExpectedRegions = list,
Description = text
],
{
{
"sarah.chen@company.com",
false,
false,
{"Northeast", "Midwest"},
"Regional manager should see two regions"
},
{
"exec@company.com",
true,
true,
null,
"Executive should have full access with amounts"
},
{
"nobody@company.com",
false,
false,
{},
"Unknown user should have no access"
}
}
),
// For each test case, temporarily override the CurrentUserEmail parameter
// by building a modified version of ResolvedPermissions
// Note: We can't literally override a parameter, but we can re-evaluate
// the permission resolution logic with a different user email
// Create a function version of the permission resolution
// This is the key insight: extract the logic into a function
ResolvePermissionsForUser = (userEmail as text) as record =>
let
RawPermissions =
Excel.Workbook(
File.Contents("C:\DataPipeline\permissions.xlsx"),
true,
true
){[Item="Permissions", Kind="Sheet"]}[Data],
TypedPermissions = Table.TransformColumnTypes(
RawPermissions,
{
{"UserEmail", type text},
{"RoleName", type text},
{"FilterDimension", type text},
{"FilterValue", type text},
{"CanSeeAmount", type logical},
{"Priority", Int64.Type}
}
),
UserRows = Table.SelectRows(
TypedPermissions,
each Text.Lower([UserEmail]) = Text.Lower(userEmail)
),
HasPermissions = Table.RowCount(UserRows) > 0,
MaxPriority = if HasPermissions then List.Max(Table.Column(UserRows, "Priority")) else 0,
WildcardRows = Table.SelectRows(
UserRows,
each [FilterDimension] = "ALL" and [FilterValue] = "*"
and [Priority] = MaxPriority
),
IsFullAccess = Table.RowCount(WildcardRows) > 0,
CanSeeAmount =
if HasPermissions
then List.AnyTrue(Table.Column(Table.SelectRows(UserRows, each [Priority] = MaxPriority), "CanSeeAmount"))
else false
in
[
HasAccess = HasPermissions,
IsFullAccess = IsFullAccess,
CanSeeTransactionAmount = CanSeeAmount
],
// Evaluate each test case
TestResults = Table.AddColumns(
TestCases,
{
{
"ActualProfile",
each ResolvePermissionsForUser([TestUserEmail]),
type record
},
{
"FullAccessMatch",
each ResolvePermissionsForUser([TestUserEmail])[IsFullAccess]
= [ExpectedFullAccess],
type logical
},
{
"AmountAccessMatch",
each ResolvePermissionsForUser([TestUserEmail])[CanSeeTransactionAmount]
= [ExpectedCanSeeAmount],
type logical
},
{
"AllTestsPassed",
each ResolvePermissionsForUser([TestUserEmail])[IsFullAccess]
= [ExpectedFullAccess]
and ResolvePermissionsForUser([TestUserEmail])[CanSeeTransactionAmount]
= [ExpectedCanSeeAmount],
type logical
}
}
)
in
TestResults
This test harness extracts the permission resolution logic into a function (ResolvePermissionsForUser) that accepts an email address as an argument instead of reading from a parameter. This is the key to testability: any logic that depends on a global state (a parameter, an environment variable) should have a functional equivalent that accepts that state as an argument.
Tip: Notice that
ResolvePermissionsForUseris defined inline as a function within theTestPermissionProfilesquery. This is intentional — we don't want the test harness to pollute the production query namespace. In larger projects, you might factor this into a separate "shared functions" query that both the production pipeline and the test harness reference.
The filtering pipeline we've built is functionally correct, but in production with large datasets, performance matters enormously. Let's discuss where this pipeline can run into trouble and how to address it.
Query Folding and the Permission Table Join
The most important performance question is: can Power Query fold the Table.SelectRows calls down to the source? If your sales data lives in SQL Server and your permission table lives in SQL Server too, the answer is potentially yes — Power Query might generate a WHERE clause that includes your allowed values directly. But the moment your permission table comes from a different source (Excel, SharePoint, a different database), folding breaks, and the filtering happens in-memory after pulling the full dataset from the source.
The solution for cross-source scenarios is to convert your allowed value lists into explicit filter parameters before issuing the source query. Instead of:
// This likely won't fold - the AllowedValues list came from Excel
Table.SelectRows(SqlTable, each List.Contains(AllowedValues, [Region]))
Do this:
// Pre-resolve the list, then construct a native query with explicit values
AllowedRegions = Permissions[DimensionFilters][Region],
// AllowedRegions is now {"Northeast", "Midwest"}
// Convert to a SQL-friendly format for direct injection
AllowedRegionsText = Text.Combine(
List.Transform(AllowedRegions, each "'" & _ & "'"),
", "
),
// AllowedRegionsText = "'Northeast', 'Midwest'"
// Use a parameterized native query
FilteredFromSource =
Sql.Database(
"prod-sql-server.company.com",
"SalesDB",
[
Query = "SELECT * FROM dbo.SalesTransactions WHERE Region IN ("
& AllowedRegionsText
& ")"
]
)
Security warning about SQL injection: The string concatenation approach above works, but it introduces SQL injection risk if your permission table values are ever editable by end users. In practice, if your permission table lives in a controlled, admin-only system, this risk is acceptable. If there's any possibility that FilterValue content comes from user input, use parameterized queries through stored procedures instead of string concatenation. This is a real-world security trade-off worth documenting explicitly.
The Full-Access Short Circuit
Notice the if Permissions[IsFullAccess] then AccessGate pattern throughout the filtering query. This is a deliberate optimization. For users like executives who have wildcard access, we skip all dimension filtering entirely. In M's lazy evaluation model, this means the List.Accumulate expression is never even computed for those users — not just that its result is discarded, but that the work of building the filtered tables is never done.
This matters when your per-dimension filtering involves expensive operations (large in-memory joins, Table.Distinct calls on big tables). Always short-circuit the expensive path when you can prove it's unnecessary.
Caching the Permission Profile
In the architecture as written, ResolvedPermissions is referenced by FilteredSalesData. In Power Query, referencing another query doesn't necessarily cache it — M might re-evaluate it multiple times depending on the evaluation context. In practice, within a single refresh, Power Query is usually smart enough to cache named query results. But if you're referencing Permissions multiple times within FilteredSalesData (as we do for IsFullAccess, CanSeeTransactionAmount, DimensionFilters, etc.), you're relying on this caching behavior.
To make caching explicit and guaranteed, you can use the let expression scoping to bind the permission record once:
let
// Bind permissions once at the top of the query
// M's scoping guarantees this is evaluated once within this let expression
Permissions = ResolvedPermissions,
// All subsequent references to Permissions within this let expression
// refer to the same evaluated value
IsFullAccess = Permissions[IsFullAccess],
CanSeeAmount = Permissions[CanSeeTransactionAmount],
Filters = Permissions[DimensionFilters],
// ... rest of the query
in
AuditedData
Binding derived values from the record at the top of the let expression is both a performance pattern and a readability improvement.
Now it's time to build this yourself. Here's a complete exercise scenario with slightly different data so you're doing the thinking, not just copying code.
Scenario: You're building a data pipeline for a healthcare organization. The source data is a patient services log with columns: PatientID, DepartmentCode, ServiceType, BillingAmount, ProviderID, and ServiceDate. The access rules are:
BillingAmountServiceType = "Billable", with BillingAmountBillingAmountProviderID), without BillingAmountStep 1: Design a permission table that handles all four roles. Define your schema with column names, types, and sample data for at least two users per role type. Think carefully about how you'll handle the ServiceType filter for billing staff — it's a value filter, not a user identity filter.
Step 2: Create the ResolvedPermissions query against your permission table. Use the schema from Step 1.
Step 3: Build the FilteredPatientServices query. Handle the deny-by-default case, the full-access case, and the standard filtered case. Make sure BillingAmount is masked to null for users who don't have the CanSeeBilling permission.
Step 4: Consider this edge case: a provider who is also a department head. Under your schema, how does the permission priority system handle their access? Should they see only their own records (provider rule) or all records in their department (department head rule)? Modify the schema and/or the filtering query to implement whichever semantics you determine is correct. Document your reasoning as comments in the M code.
Step 5: Create a five-row test harness using the function extraction pattern from the lesson. Verify at least one positive case (user sees correct data) and one negative case (user with no permissions gets empty table).
Mistake: Using case-sensitive email comparison
[UserEmail] = CurrentUserEmail will fail silently if the casing doesn't match exactly. Always use Text.Lower on both sides. Some organizations also have trailing whitespace issues in directory data — wrapping in Text.Trim(Text.Lower(...)) is a good defensive habit.
Mistake: Returning an error instead of empty data for unauthorized users
When a user has no permissions, returning an error (error "Access denied") is tempting but wrong for a data pipeline. The downstream consumer (a report, a dashboard, a downstream query) has to handle errors specially. Return Table.FirstN(TypedData, 0) instead — this gives you an empty table with the correct schema. Columns are still there, just with zero rows. Reports render gracefully, aggregations return zero, and the system degrades cleanly.
Mistake: AND logic when you need OR logic (and vice versa)
Re-read the section on union semantics. This is the most common logic error in permission systems. Always ask: "If a user has two different filter dimensions, should they see the intersection or the union?" The answer depends on your business domain and should be explicit in your documentation.
Mistake: Not handling the case where DimensionFilters is empty
If IsFullAccess is false and DimensionFilters is an empty record [], your List.Accumulate runs zero iterations and returns AccessGate unfiltered — which means the user sees everything. This is a bug. Add a guard:
DimensionFilteredData =
if Permissions[IsFullAccess]
then AccessGate
else if Record.FieldCount(Permissions[DimensionFilters]) = 0
then Table.FirstN(TypedData, 0) // No filters defined = no access
else
// ... normal filtering path
Troubleshooting: Query takes forever to refresh
First, check query folding. Right-click each major step in Applied Steps and see if "View Native Query" is available. If it stops being available at a certain step, that's where folding breaks. Second, check how large your permission table is. If it's hundreds of thousands of rows (it shouldn't be — the permission table is always tiny), something is wrong with your data model. Third, check whether Table.Distinct is necessary and whether you can replace it with a union-specific deduplication using the TransactionID primary key.
Troubleshooting: Record.Field(_, dimension) throws an error
This happens when the dimension value in DimensionFilters doesn't match a column name in your source data. For example, if the permission table has FilterDimension = "region" (lowercase) but your sales table has the column Region (title case). Add a validation step after resolving permissions that checks all filter dimensions against the actual column names of the source table:
ValidateDimensions =
let
SourceColumns = Table.ColumnNames(RawSalesData),
FilterDimensions = Record.FieldNames(Permissions[DimensionFilters]),
InvalidDimensions = List.Difference(FilterDimensions, SourceColumns)
in
if List.Count(InvalidDimensions) > 0
then error Error.Record(
"PermissionConfigError",
"Invalid filter dimensions: " & Text.Combine(InvalidDimensions, ", "),
InvalidDimensions
)
else null,
Add this as a step that runs before the filtering logic, and use ValidateDimensions as a dependency (reference it in a step that uses it, even if just in a comment) so M's lazy evaluator actually runs it.
Let's consolidate what you've built and why each piece matters.
You now have a three-layer permission architecture: identity resolution (the parameter), permission resolution (the ResolvedPermissions query that produces a clean record), and data filtering (the FilteredSalesData query that applies the record to actual data). These layers are independently testable, independently swappable, and independently understandable by someone reading the code cold.
The filtering pipeline handles four distinct cases: no access (deny by default, empty table), full access (wildcard, skip filtering entirely), AND-logic multi-dimensional filtering (accumulator pattern), and OR-logic multi-dimensional filtering (union pattern with deduplication). The column-level masking sits as a separate layer on top of row-level filtering, which is the correct separation of concerns.
The test harness pattern — extracting runtime-dependent logic into a function that accepts its dependencies as arguments — is a general principle of testability in M that applies far beyond this specific use case. Any time you have a query that depends on a parameter, a current date, or an environment value, consider whether you can extract the core logic into a function for testing purposes.
For your next steps, consider these extensions:
Audit logging: Add a step that writes the permission profile (user, timestamp, applied filters) to an audit log table. This is often a compliance requirement and is straightforward to implement as an additional query that writes to a SharePoint list or SQL table.
Group expansion: If your organization uses Azure AD groups, build a pre-processing query that calls the Microsoft Graph API to expand group memberships into individual user rows before the permission table is consumed. This query should refresh infrequently (daily is usually enough) and its output should be cached.
Integration with Power BI model-level RLS: This pipeline is most powerful when used in addition to, not instead of, Power BI's native DAX-based RLS. Use M-level filtering to reduce data volume before it hits the model, and DAX RLS as the true security enforcement layer. Defense in depth.
Dynamic column filtering: Extend the CanSeeAmount pattern into a full column permission system where the permission table defines which columns each role can see. Build a step that selects only the permitted columns using Table.SelectColumns with a dynamically resolved list. This requires a different permission table schema but the principles are identical.
The architecture you've built here is not just a Power Query trick. It's a pattern for building secure, maintainable, auditable data pipelines in any technology that supports functional data transformation — which is most of them. The specific M syntax will vary across tools, but the separation of identity resolution, permission resolution, and data filtering as distinct, composable concerns will serve you everywhere.
Learning Path: Advanced M Language