Power BI: Replace Dataflow Gen1 + RLS?

Key insights

• Power Query Editor in Excel for Web: Use the full Power Query experience in Excel for Web to build refreshable, shared transformations that behave like a dataflow.
  Connect those tables to Power BI datasets so users can load or refresh data securely.
• Dataflow Gen1: This hack addresses the deprecation risk and RLS gaps in Dataflow Gen1 by relying on core Power BI features instead of legacy dataflows.
  It helps teams avoid migration surprises and costly replacements.
• Analysis Services queries: The method queries the semantic model to extract individual tables directly from a published dataset, even from Pro workspaces.
  This keeps transforms close to the model and preserves dataset-level security during refreshes.
• RLS enforcement: Row-level security configured on the dataset is honored during refreshes and when users connect to tables, so each user only sees permitted rows.
  This makes the approach suitable for multi-tenant or compliance-sensitive reports.
• USERPRINCIPALNAME(): Use dynamic DAX functions and a security lookup table to map user identities to allowed rows, and assign Azure AD groups or users to roles.
  Test roles with "View as Roles" and automate membership with AD or HR sources when possible.
• Governance and performance: Validate refresh behavior, monitor performance for import scenarios, and keep security tables and refresh schedules up to date.
  Plan capacity and dataset design to avoid bottlenecks and maintain strict access control.

Video Summary and Context

In a recent YouTube presentation, Wyn Hopkins [MVP] demonstrated a practical workaround that could replace Dataflow Gen1 and add native support for row-level security (RLS) in many Power BI scenarios. The video walks through using the Full Power Query Editor in Excel for Web together with Power BI datasets to replicate dataflow behavior while honoring RLS filters. Consequently, this approach targets organizations that face the twin pressures of an impending deprecation of Dataflow Gen1 and the need for secure, governed access to shared tables. Importantly, the walkthrough aims to be reproducible in standard Power BI environments, including Pro workspaces in certain cases.

Hopkins frames the technique as a way to keep data preparation and distribution inside core Power BI capabilities while avoiding potential new costs tied to replacement products. He also provides practical artifacts for viewers, such as a sample PBIX file and step-by-step demonstrations of the M code and dataset configuration. Therefore, readers should view the hack as a pragmatic engineering choice rather than a one-size-fits-all replacement. Overall, the video stresses balancing security, refresh behavior, and user experience.

How the Technique Works

The method centers on publishing a Power BI dataset that already enforces RLS, and then using Power Query to extract individual tables from that semantic model. First, the author defines roles and DAX filters in Power BI Desktop so that datasets return only the rows each user should see. Then, Power Query or Excel for Web connects to that secured dataset and surfaces the available tables for consumers to use in import scenarios. As a result, users can refresh data or build reports without bypassing the model-level security.

In addition, the video shows how to use Analysis Services-style queries inside Power Query to pull tables directly from the semantic model, which makes the solution more flexible than static exports. This approach allows end users to create their own reports while relying on the published dataset for governance. Consequently, refresh operations inherit RLS behavior, addressing a major limitation of Dataflow Gen1. The tutorial also highlights practical steps such as testing roles with “View as Roles” before publishing.

Benefits and Practical Advantages

One clear benefit is that the method enforces RLS during both reporting and refresh operations, so organizations reduce the risk of inadvertently exposing sensitive rows. Moreover, because the solution uses existing Power BI features, teams can avoid migrating immediately to new paid services that might replace Dataflow Gen1. Therefore, organizations can maintain continuity for shared data prep without adding licensing overhead in the short term. The video emphasizes that this pattern is particularly useful for scenarios where security and refresh-able import tables are both required.

Another practical advantage is improved user autonomy: end users gain the ability to connect to and refresh curated tables while still being constrained by model-level filters. Consequently, teams can decentralize report creation without sacrificing governance. Hopkins also points out that the hack works for common enterprise setups when security tables map users to rows via functions like USERPRINCIPALNAME(). Thus, the approach aids scalability by integrating with directory services and existing security metadata.

Tradeoffs and Implementation Challenges

Despite its benefits, the technique carries several tradeoffs that organizations must weigh carefully. For instance, relying on a dataset that enforces RLS can complicate development workflows because transformations live in the semantic model rather than in a centralized dataflow. Consequently, teams may face governance and versioning challenges when many datasets duplicate similar Power Query logic. Additionally, complex transformations and large datasets can increase refresh times and put pressure on workspace capacities, which affects performance.

Furthermore, the approach depends on the current behavior of Power BI services and could be impacted by future platform changes or licensing shifts. Therefore, IT leaders should treat this as a pragmatic interim solution rather than a guaranteed long-term architecture. Another challenge is that managing RLS at scale requires careful maintenance of security tables and role definitions, which introduces administrative overhead. Ultimately, balancing security, maintainability, and performance requires clear policies and disciplined processes.

Implications for Organizations and Next Steps

For organizations facing the deprecation of Dataflow Gen1, this hack offers a viable path to preserve secure, refreshable shared tables without incurring immediate new costs. However, teams should pilot the approach on representative datasets and measure refresh performance, role administration effort, and development overhead. In this way, they can validate whether the tradeoffs align with their governance model and operational capacity. The video’s downloadable PBIX and worked examples can accelerate that pilot process for technical teams.

In conclusion, Wyn Hopkins [MVP] presents a thoughtful, hands-on alternative that leverages core Power BI and Power Query capabilities to meet both security and sharing needs. As a result, organizations gain another tool for managing the transition away from legacy dataflow patterns while protecting sensitive data through RLS. Nevertheless, leaders should evaluate long-term strategy, monitor platform updates, and plan for maintainability before adopting this pattern widely. Ultimately, the technique is a useful addition to Power BI practitioners’ toolkits when used with proper governance and testing.

Keywords

Power BI Dataflow Gen1 replacement, Power BI row-level security, Power BI RLS setup, Dataflow Gen1 to Gen2 migration, Power BI dataflows alternative, Power BI dynamic row-level security, Power Query dataflows hack, Power BI security for dataflows