MS Excel table within Power BI

[Anjan_Madhwesh]

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.

[Poojara_D12]

Hi @Anjan_Madhwesh 

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.

[Poojara_D12]

Hi @Anjan_Madhwesh 

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.

[Anjan_Madhwesh]

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.

[v-mdharahman]

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 Team

[Anjan_Madhwesh]

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. 

[lbendlin]

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com

[Anjan_Madhwesh]

No no - Microsoft needs to approach its customers itself - I'm hoping they reach out to me since I'm offering my services free of charge to help them improve their product.

No company should think it's too big or lofty to ignore customers who are trying to help it become a better product / service provider.  I am an expert user who preempted this move to Fabric by creating a similar directquery / directlake type environment long before Fabric was introduced. I think I know a thing or two.

[v-mdharahman]

Hi @Anjan_Madhwesh,

We truly appreciate your offer to work together. Although this is a community site and most of us here are peers or MVPs volunteering, I would recommend that Microsoft team members and PMs think of contacting domain experts like you, who are interested in helping improve the product.
Meanwhile, I'd like to mention that since there are thousands of global customers with each having their own different ideas and use scenarios, Microsoft has designed the Ideas forum as a hub area to collect, monitor, and prioritize requests for features. If you've not yet done so, I'd like to respectfully invite you to submit your idea there. The product team reviews submissions frequently, and when they notice ideas with great merit or influence, they do take a further step.

Your feedback obviously comes from great experience, and input such as yours is precious in helping to form the future of the product. Once again, thank you for taking the time and sharing your thoughtful opinion, we all gain from voices like yours in the community.

Best Regards,

Hammad.

[Anjan_Madhwesh]

That is a kind and thoughtful response, Hammad. We have already opened a ticket with Microsoft - I'll see if my organization - that has a bit more leverage than some open forum - can take this further this with Microsoft.

[v-mdharahman]

Hi @Anjan_Madhwesh,

Thanks for the update and for confirming you opened a support ticket with Microsoft. Take your time, and feel free to reach out whenever you're ready to continue. We will be happy to assist further once Support team had a chance to look into it. 

Best Regards,

Hammad.

[v-mdharahman]

Hi @Anjan_Madhwesh,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

Best Regards,

Hammad.

[Anjan_Madhwesh]

Hi Hammad, Microsoft opened a ticket TrackingID#2402230050002261. However they promptly closed it citing the issue as known limitation and asked us to submit a design change recommendation.

[v-mdharahman]

Hi @Anjan_Madhwesh,

Thank you for coming back and updating us on the status of your support ticket. As Microsoft support team mentioned its a known limitation, you can raise a topic on Ideas Forum or up-vote an existing topic which is related to your issue.

Best Regards,

Hammad.

[Anjan_Madhwesh]

nope - see above my response above - Microsoft needs to reach out to me. I think we are going around in circles on this topic. Either solve it or move on knowing you can't really solve it.

[lbendlin]

Did you submit the Business Impact Statement?

[Anjan_Madhwesh]

So do you have an answer for me?

[Anjan_Madhwesh]

Can I ask a question - are you actually able to help with this at all?

In any case - here's the response from Microsoft from March 2024:

"

Issue Definition:

Power BI data export as an xls, M parameter part is missing.

Analysis and Resolution: 

I have submitted your request to the Power BI Customer Advocacy Team on your behalf. Power BI team reviews feedback from multiple channels including our public Ideas site , customer engagements and market research. We incorporate and prioritize all of this feedback which is published into our public roadmap in the spring and fall . The team will review your feedback and provide a response to your technical account manager. Getting a response to the request might take couple of months while it is researched and reviewed. 

I want to set expectations early that these requests may not be approved. I recommend looking at workarounds that will let you continue to proceed with your project now, so that you don't lose traction if the request isn't approved, or even if it gets approved and until gets implemented. 

Please also note that if a request is approved but it's not already scheduled within a current development wave, we won't be able to provide an ETA until the next wave is fully planned next semester. It may also get approved into the long-term backlog, for which there are no ETAs until future planning cycles. 

"

[Anjan_Madhwesh]

Thank 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. 

[Anjan_Madhwesh]

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.

[lbendlin]

There are plenty of custom visuals that will happily take your money to provide that functionality.  What's the business value though?