Power BI Dataset in Excel - Remove Table[Column] Prefix in Headers

[regerror6]

I have a dataset published to the Power BI service—which I am trying to insert as a table in Excel.  Everything works as expected except all the column names have a Table[Column] naming convention.  The only method I have found to display just the column name (without the fully qualified prefix) is to query the inserted table in Power Query and return the result as another table in the workbook.  This is completely redundant and will confuse my end users.

I suspect I could edit the DAX in the connection, but there are 120+ columns—which have a high likelihood of changing.  In turn, I'd rather not take that approach.

Has anyone found a solution to this scenario?  Thanks!

[Anonymous]

Hi @regerror6,

Thank you for reaching out in Microsoft Community Forum.

Thank you  @ArwaAldoud  for the helpful response.

Currently, Power BI datasets cannot be imported directly into Power Query. below are the possible approaches

1. Loading the dataset to a worksheet first via Data > Get Data > From Power Platform > From Power BI, then referencing it in Power Query for transformations.

2. Using Analyze in Excel if your goal is to work with PivotTables instead of raw tables.

Unfortunately, there’s no direct way to connect Power BI datasets to Power Query without inserting them into a worksheet first.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[ArwaAldoud · Accepted solution]

Hi @regerror6 

Unfortunately, Power BI automatically adds the Table[Column] format when inserting a dataset into Excel, and there isn't a built-in way to remove it directly. However, you can manually rename the columns (not ideal for large datasets), use Power Query, or automate the process with VBA.

Let me know if you need guidance on these methods

If this solution was helpful, please accept it as a solution or give kudos to help other community members

[regerror6]

Thanks for the insight, @ArwaAldoud!  Do you know of any way to import the dataset directly into Power Query (instead of landing it on a worksheet first)?  I'm surprised Power BI is available in Excel (Data > Get Data > From Power Platform) but not in the New Source list in Power Query.  I'm playing with Data Flows but hitting a dead end.

[Stuart_S]

I would like to add that this is a very frustrating problem for me that is preventing me from using this feature.

[ArwaAldoud]

You're welcome @regerror6 
Unfortunately, importing a Power BI dataset directly into Power Query without first landing it in a worksheet isn’t natively supported. However, if you have a Fabric (Power BI) license, specifically Premium or PPU, you can use XMLA endpoints to connect directly to the dataset.
Another option is using Dataflows, but if you're hitting a dead end, you might also explore Analyze in Excel. 

If this solution was helpful, please accept it as a solution or give kudos to help other community members