Enhancements for Excel Pivot Table Connectivity to Semantic Models
The business needs Excel to perform on Fabric The Problem: Unoptimized Capacity Consumption and Performance Bottlenecks While Excel's live connection to Fabric Semantic Models is a key feature, it currently presents a significant challenge for enterprise-level deployments, primarily related to performance and the unoptimized use of Fabric capacity. The core issue is that the current connection method, which relies on the XMLA endpoint and MDX query translation, leads to: * Excessive Capacity Consumption: Every user interaction—from dragging a field to applying a filter—generates an independent, and often verbose, MDX query that is translated and executed. This constant, unoptimized querying on a per-user, per-click basis results in a high number of interactive queries, which are the most expensive type in Fabric capacity. * Poor User Performance: As a result of the unoptimized queries, users experience frustratingly slow response times. A simple action that should be near-instantaneous can take several seconds to complete, especially with complex semantic models and multiple concurrent users. This degrades the user experience and can lead to a lack of trust in the centralized data model. The Proposed Enhancement: A Modern, Optimized Connection Layer We request the development of a new, modernized connection layer for Excel that is purpose-built for Fabric Semantic Models. This would move beyond the legacy MDX translation approach and provide a more intelligent and efficient way for Excel to interact with the service. Key features of this enhanced connection layer should include: * Smart Query Batching: Implement a mechanism to intelligently batch multiple user actions into a single, optimized query to the Fabric service. This would significantly reduce the number of interactive queries, lowering capacity consumption and improving performance. * Native DAX Support: Instead of translating MDX to DAX on the server, allow Excel to generate optimized DAX queries directly from the client. This would eliminate a performance bottleneck and ensure that the queries are written in a way that best leverages the semantic model's structure. * Improved User Experience: A faster, more responsive connection would dramatically improve the user experience, encouraging broader adoption of Fabric as the go-to platform for data analysis in conjunction with Excel. Business Impact: Unlocking True Enterprise Scalability This enhancement is not just a "nice-to-have"; it is critical for unlocking the true enterprise scalability of Microsoft Fabric. A modern, performant connection between the world's most popular spreadsheet tool and Microsoft's unified data platform would: * Reduce TCO: By consuming capacity more efficiently, organizations can get more value from their Fabric investment and manage costs more effectively. * Boost Productivity & Trust: Faster performance means business users can work more efficiently, building trust in the centralized data model and reducing reliance on manual, ad-hoc data sources. * Strengthen the Microsoft Ecosystem: This would further cement the powerful synergy between Fabric and Office, demonstrating a seamless and high-performance experience for the millions of users who rely on both products daily. Summary: Everyone agrees, Excel is not going away. Optimizing Excel's connectivity to Fabric Semantic Models for capacity and performance is essential. We urge the Fabric team to prioritize the development of a modern, efficient connection layer that will not only solve current user frustrations but also enable organizations to confidently scale their analytics solutions. Fabric has gone miles beyond a reporting tool as it has evolved into Fabric, these changes fully fit within the scope of providing a robust end to end Analytics platform.Streamlining Power BI Tenant Inventory Management Using a REST API Connector
Background & The Challenge In one of my projects as a Power BI Administrator, a common requirement was to maintain a comprehensive inventory of tenant assets, including workspaces, reports, datasets, dashboards, and capacity metrics. Historically, my team handled this using a legacy, multi-step extraction process: Data Extraction: Running manual PowerShell scripts to call various Power BI REST API endpoints. Storage: Dumping the raw JSON outputs into a SQL Server database. Analysis: Writing ad-hoc SQL queries against the database whenever we needed specific inventory information. Action: Using this queried data to perform quarterly clean-up tasks (e.g., deleting orphaned workspaces, updating capacity assignments, and removing unused reports). The Pain Point This legacy approach was highly inefficient. Every time we needed updated inventory data, an admin had to validate the PowerShell scripts, manually execute them to refresh the SQL database, and then write or run SQL queries to get actionable insights. The data was often stale, and the process was too reliant on manual intervention. The Proposed Solution To eliminate the manual overhead and the need for external database storage, I designed a streamlined solution: Consolidating the inventory tracking directly into a Power BI report using a Custom Connector. Instead of pushing API data out to SQL via scripts, the solution pulls the data directly into Power BI: Custom Power BI Connector: I developed a custom connector designed specifically to authenticate and call the required Power BI REST API endpoints (Workspaces, Datasets, Reports, etc.). Direct Reporting: I built an end-to-end Power BI report that consumes data directly from this custom connector, visualizing all necessary inventory and capacity metrics in one place. Automated Refresh: I configured an On-Premises Data Gateway connection to allow the dataset to refresh automatically on the Power BI Service. The Value Add Zero Manual Intervention: No more managing or executing PowerShell scripts. The data stays fresh via standard Power BI scheduled refreshes. Self-Service Access: Whenever stakeholders or admins need inventory details for quarterly cleanups, they simply open the shared Power BI report. Actionable Insights: Admins can instantly identify unused assets, capacity bottlenecks, and workspace bloat without writing a single SQL query. Key highlights: 🔐 Azure AD OAuth2 - no client secrets, no passwords 📄 Full inline documentation inside Power BI Desktop 🔄 Auto-pagination for large tenant environments ⚡ Works with both Desktop & On-premises Data Gateway Some Screenshots of POC I successfully built and tested this approach as a Proof of Concept (POC) end-to-end. I am sharing this idea with the Fabric community for others who might be struggling with tenant inventory management and looking for a more automated, native reporting approach. Hopefully, This will be help lot of members who is facing similar type issues if Microsoft team can officially launch this connector so everyone can just use and do whatever they need. Quick Update: I have created the custom conenctor for the Power BI as well as Fabic REST endpoints. As of not it covers... -> 56 Power BI REST API functions (Datasets, Reports, Dashboards, Pipelines, DAX execution & more...) -> 53 Microsoft Fabric functions (Lakehouse, Warehouse, Eventhouse, KQL & more...) Shoutout / Special Thanks: Boston Office D365 User Group www.youtube.com/@BostonOffice365UserGroup Note: AI was used to assist in formatting and refining this post for clarity.Built a free tool to inventory & risk-rate legacy SSIS packages before a Fabric migration
Hi all, Sharing something I built that might save some of you the painful "open every .dtsx in SSDT one by one" phase of an SSIS → Fabric migration. The problem it addresses: most teams sitting on a legacy SSIS estate have dozens to hundreds of undocumented packages and no clear starting point. Before any actual migration work, someone has to answer: what does each package do, which components map cleanly to Fabric, and which need a full rewrite? What it does: Parses .dtsx package XML and extracts control flow tasks, data flow components, connection managers, variables, and precedence logic Maps each component against a Fabric equivalent — Data Flow Task → Dataflow Gen2, Execute SQL Task → Script/Stored Procedure Activity, Script Task → Notebook Activity, Foreach Loop → ForEach Activity, and so on Gives each package a Low/Medium/High risk rating based on concrete signals (Script Task usage, Fuzzy Lookup, deprecated CDC/Attunity components, nesting depth, dynamic connection strings) Flags every component individually as auto-mappable or needs-manual-redesign, not just the package as a whole Across a batch of packages, produces a portfolio rollup: risk tier counts, the blockers showing up across the most packages, deprecated components flagged separately, and a suggested migration order What it's built as: a Claude Skill (Anthropic's Claude AI), with the actual XML parsing done by a plain Python script with zero dependencies — not an LLM guessing at package structure. It's designed to run fully offline, no live Azure/Fabric API access assumed, though it flags anywhere that access would improve accuracy (resolving a dynamic connection string, for example). What it deliberately doesn't do yet: this covers the Inventory/Classify phase only. It won't draft actual Fabric pipeline JSON or claim a package is production-ready — every future draft artifact gets an explicit DRAFT/NEEDS REVIEW label. I'd rather it under-claim than over-claim. Repo's on GitHub with a sample package and example report included, so you can see the actual output before running it against your own packages: https://github.com/HBBH11/ssis-fabric-migration-assistant Genuinely interested in feedback from people who've done these migrations for real — particularly whether the risk heuristics hold up against messier packages than my test case, and whether the Fabric equivalence mappings match what you've found in practice. Happy to take suggestions on what a Phase 2 (draft pipeline generation) should prioritize too.
Allow Downloading Power Query (M) Transformations Along with Power BI Reports from the Service
Category: Power BI Service / Data Connectivity Description: Hi Power BI Community Team, I would like to suggest a feature enhancement for Power BI Service that would be useful for Power BI developers, BI teams, and report maintainers. Currently, when downloading a report from Power BI Service, users working with live-connected reports may not receive the underlying semantic model or its Power Query transformations. It would be helpful if Power BI Service provided an option to export the associated Power Query M scripts along with the report, subject to the user's permissions and the semantic model's configuration. Proposed Enhancement: Allow users to export Power Query M scripts associated with the underlying semantic model when downloading a report from Power BI Service. The export could be provided as a separate .pq or .txt file, or as part of a fully editable PBIX download wherever supported. The export should preserve query names, transformation steps, and query dependencies. Business Value: This feature would help developers understand and maintain existing ETL logic, reduce the effort required to recreate transformations when the original PBIX file is unavailable, and support troubleshooting, report migration, documentation, and knowledge transfer. For example, when a developer downloads a live-connected report from Power BI Service, having an authorized way to retrieve the Power Query transformations would eliminate the need to locate the original PBIX file or contact the original report developer. I understand there may be technical and security considerations. Even a separate, permission-controlled export of Power Query M scripts would be a valuable enhancement. Could this functionality be considered for a future Power BI update? Thank you for your time and consideration.
Request to add a feature to extract gateway connection status and last time credentials used
Could you please add a feature in the admin UI on Microsoft Fabric/Power BI to extract the Gateway connections with details of the gateway connection status and last activity information along with the connection name, users, and connection type. Current Limitation Administrators can manage gateways through the Power BI Service, but there is currently no simple way to use PowerShell to extract operational details such as: Gateway connection status (Online/Offline) Last successful connection timestamp This capability is important for our internal stakeholders from an audit and governance perspective, as it would help maintain a streamlined and well-governed Power BI environment. As part of the company's cloud modernization initiative, our internal teams are planning to extract connection information to identify which connections are still required and which can be safely decommissioned. This analysis is necessary to identify on-premises server dependencies and develop a migration plan to the cloud. Currently, because there is no way to determine which connections are active and which are inactive, it looks like the exercise becomes complex. Having this functionality available would greatly improve visibility, governance, and migration planning efforts. Thanks.Support Fabric SQL Database with workspace-level inbound Private Link
Fabric SQL Database supports tenant-level Private Link but not workspace-level Private Link. Securing a small number of databases therefore requires enabling Private Link across the entire tenant, introducing unnecessary operational constraints for unrelated workspaces, Power BI workloads and on-premises data gateways.
Expose Power Query as a Standalone Runtime and Command-Line Interface (CLI)
Summary Power Query has evolved into one of Microsoft's most powerful data transformation technologies and is now used across Excel, Power BI, Fabric, Dataflows, Power Platform, and other products. However, Power Query can only be executed through a host application, despite the existence of a mature M language and execution engine. I would like Microsoft to expose the Power Query engine as a first-class standalone runtime and provide an officially supported command-line interface (CLI) and API. The Problem Today, Power Query transformations are often embedded inside: Excel workbooks Power BI Desktop files Fabric Dataflows Power Platform Dataflows While this works well for interactive users, it creates challenges for enterprise-grade automation. Many organizations would like to: Schedule Power Query transformations without opening Excel. Run Power Query from PowerShell scripts. Integrate Power Query into CI/CD pipelines. Execute transformations on servers without Office dependencies. Reuse Power Query code across multiple solutions. Treat Power Query as a reusable transformation layer rather than a workbook artifact. Currently, Power Query feels like a language without an officially supported runtime, even though the engine already powers multiple Microsoft products.
Support Native SQL LIKE / NOT LIKE Wildcard Filtering Across Power BI Reports
Description Currently, Power BI does not provide a native way for report consumers to perform dynamic SQL-style wildcard filtering using patterns such as LIKE '____' - Above return all values with exactly four characters LIKE '__A_' - Return all four-character values where the third character is "A" Many enterprise reporting scenarios require users to enter dynamic search patterns similar to SQL LIKE and NOT LIKE functionality. While workarounds using DAX, calculated columns, custom visuals, or custom logic may be possible for individual fields, these approaches are not scalable when reports contain hundreds of filterable columns. Current Limitation After engagement with Microsoft Support, Advisory, and Architecture teams (Case #2606220040001996), it was confirmed that: Power BI currently has no native runtime implementation of SQL LIKE / NOT LIKE filtering. Existing alternatives do not adequately support large-scale implementations with hundreds of columns. Third-party visuals such as Smart Filter do not fully support SQL-style wildcard matching (for example _ as a single-character wildcard). This has been identified as a current product limitation. Suggested Enhancement Provide native filtering capabilities that support standard SQL wildcard expressions within Power BI report filters and slicers, including: % for multi-character matching _ for single-character matching LIKE and NOT LIKE style filtering behavior User-entered runtime pattern matching Consistent functionality across all text columns without requiring custom DAX or calculated columns Example Scenarios User Input Expected Result ____ Return all values with exactly 4 characters __A_ Return all 4-character values where the third character is A %ABC% Return values containing ABC A% Return values starting with A %XYZ Return values ending with XYZ Business Impact This enhancement would provide significant value for organizations with large-scale enterprise reporting environments by: Reducing reliance on custom DAX and calculated columns Improving report usability for business users familiar with SQL-style searches Enabling dynamic filtering across hundreds of columns without additional development effort Improving scalability, maintainability, and performance of enterprise Power BI solutions Supporting Information Microsoft Support has confirmed that there is currently no supported native Power BI solution for dynamic SQL LIKE wildcard filtering at scale and recommended submitting this requirement through the Microsoft Fabric Ideas portal for product team evaluation.
