Forum Discussion
MS Excel table within Power BI
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
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.
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
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.
