Forum Discussion
MS Excel table within Power BI
Currently, the table and matrix visuals in Power BI are not nearly as powerful as MS excel but Power Bi is very good with large data. So why not allow a table visual within Power that can Mimic MS excel. For e.g. user can setup auto filter on columns and search what they want (instead of the separate filters visuals that is quite annoying to use). Another thing that the users would like to do is vlookup feature.
22 Replies
You can connect to your semantic model via Excel and work as usual with Excel pivot table.
Have you tried it yet?
RegardsThere are plenty of custom visuals that will happily take your money to provide that functionality. What's the business value though?
Wow thanks for such quick responses. I guess I forgot to mention in post earlier that Power BI has a defect. When you try to connect to the semantic model that connects to Oracle over a DirectQuery connection and you try the analyze using excel option, Excel is unable to properly pass the where clause to Oracle. Microsoft has acknowledged this as a defect but hasn't provided a fix.
Power BI Matrix and table are notoriously ill equipped for analysis when compared with MS Excel.
On a separate note, the inability to add custom columns in service (viewer role) is ununderstandable to me when it's happily allowed if we provide a desktop template with the same semantic model.
Hi Anjan_Madhwesh,
Thanks for reaching out to the Microsoft fabric community forum and for the detailed post. you are raising several valid points that many users share.
Regarding the Excel-like filtering and search within Power BI tables, you're right that native visuals don't support auto filters like Excel. While slicers and filters are the current workaround, they can be clunky. There are third-party visuals like Power ON, Inforiver, or Zebra BI that offer enhanced interactivity, including in-cell filtering and Excel-style navigation. These are paid, but some offer free trials if you want to evaluate them.
For VLOOKUP-like behavior, Power BI uses relationships and DAX functions like RELATED, LOOKUPVALUE, and MERGE in Power Query. These can replicate VLOOKUP logic, but they require setup in the data model or query editor.
On the Excel integration defect with DirectQuery to Oracle yes, this is a known limitation. Excel struggles to push filters correctly through DirectQuery, especially with Oracle. Microsoft has acknowledged it, but there’s no fix yet. A workaround is to use Import mode if feasible, or create a composite model with a small imported subset for Excel analysis.
As for custom columns in Power BI Service: Viewer role doesn’t allow model edits, including calculated columns. This is by design for governance and performance reasons. If you need users to interact more deeply, consider using Personalize visuals or upgrading their role to Contributor or Member in a workspace.
I would also take a moment to thank lbendlin and sergej_og, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support TeamThank you - first reasonable reply. Unfortunately the data volume is so large that import mode is not practical. Generally - I'm not support of this import model that relies on making a copy of data inviting concurrency issues. In this day and age where compute is not longer a limitation, BI should focus on real time visualization.
The roles are limitation here - if we want to allow the users some flexibility where as we supply curated data which is controlled, its not possible to do on service. The desktop is too complex normal human interaction - its really meant for a "developer" someone who spends their life on the tool not for a business user who just wants to interact with the data.
You all have miles to go but you have one advantage - the competition (other than Palentir) is not nearly there even where you are yet.
Microsoft needs to heavily invest in user experience since this tool is actually quite good. Almost all interfaces are designed for people who know where to look to get something done and not for a user who simply wants to get something done. But wait there is more - once one figures out how to get something, we hit roadblocks such as the one I explained above (sort of "thusforth but no further" moments).
If you are really interested in improving the product, please do get in touch with me. I'm happy to point out area of improvement patiently. I'll quite enjoy contributing to a good cause but I do believe in the product.
If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com
Currently, table and matrix visuals in Power BI lack the flexibility and interactivity that users are accustomed to in Microsoft Excel, particularly when working with tabular data. While Power BI excels at handling and visualizing large volumes of data efficiently, its table-like visuals are not as user-friendly or dynamic. Many users express the need for Excel-like features such as auto-filtering directly on column headers, allowing them to quickly search and filter values without relying on separate filter panes or slicers—which can feel clunky and disrupt the flow of analysis. Additionally, a common request is for a feature similar to Excel’s VLOOKUP, where users could directly cross-reference values across tables in a more intuitive and interactive manner, rather than relying on pre-built DAX measures or complex relationships. Introducing these familiar capabilities into Power BI would significantly improve usability for business users, especially those transitioning from Excel, and make the platform more powerful for ad hoc exploration and analysis.
Fake response using AI to increase your "super user" footprint. Your answer is totally useless. I'm astonished on the different levels that cheating can morph into.
You're absolutely right in highlighting a key gap between Power BI and Excel when it comes to interactive tabular data exploration. While Power BI excels at handling large datasets, real-time visuals, and dynamic dashboards, its table and matrix visuals lack the flexibility and interactivity that Excel users are accustomed to. Features like column auto-filters, inline search, and Excel-like VLOOKUP functionality are highly intuitive and make data exploration far more accessible, especially for business users who are used to Excel. In Power BI, the reliance on separate filter panes or slicer visuals can feel disjointed and less user-friendly. The idea of having a Power BI table visual that mimics Excel—with native column filters, quick sorting, and even simple lookup capabilities—would greatly enhance usability and reduce the need for extra DAX or complex navigation. Such enhancements could bridge the gap between data visualization and ad-hoc analysis, making Power BI more approachable for Excel-savvy users. While Power BI is built primarily for visual analytics rather than spreadsheet-style exploration, integrating these familiar features could significantly improve adoption and user satisfaction, especially in enterprise environments where Excel is still heavily used for detailed data investigation.
