Creating excel tables from Published PI Reports

[homboy27]

I have a dashbaord that is published on power bi workspace. I have then created tables from the dashboard by linking to the powerbi workspace. Once I refresh the powerbi, I open the excel and refresh the tables and it works great. However One issue I am having is what if I want to make changes to tables such as I want to add in a new column or remove a column. I cannot figure out how to make edits to the table like I can in a Pivot table. Can someone please help?

[homboy27]

Above is how I created the table. But once the table is created, how do I get back to this step above in case I want to add in new columns to the table?

[homboy27]

I tried to create a pivot table and would like to apply filters to it but I get the below error. How do I fix this?

@MasonMA @Aburar_123 

[MasonMA]

Hello @homboy27 

what types of filters were you trying to use? Excel might not be able to calculate Value Filters on its own since it's a live OLAP connection. 

If this is your case, try creating the required measure (like “Total Sales” or “Top N”) in Power BI Desktop and republish the dataset. 

[homboy27]

I am trying to create a filter for example on customers with sales greater than $100,000

[MasonMA · Accepted solution]

Does this already exist in your dataset as a Measure? if not then try creating a Measure like 

Total Sales =
SUM ( Table[SalesAmount] 

first in the dataset. That way the calculation is handled by the dataset, and Excel will respect it

[homboy27]

yes it already exisits in my data set as a column. who does that it not work if its already in the dataset?

[MasonMA]

Hello @homboy27 

When you connect Excel to a published Power BI dataset, Excel is only acting as a viewer. The schema (what columns/measures exist) comes entirely from the Power BI model, which means you can’t just add/remove columns in Excel the way you can in a PivotTable. Excel isn’t allowed to change the dataset itself.

To change the columns you see, you need to go back to the source dataset and modify the Power BI Dataset with simply steps below, 

1.Open pbix. file.

2.Go to Model View.

3.Add/Remove columns as needed,

To add a column: Use Power Query (if the field exists in the source) or DAX (via “New Column”).

To remove a column: Either delete it from the model or just hide it from report view if you still need it for calculations.

4.Save and Publish back to the same workspace.

5.In Excel, Refresh All and your new/removed columns will show in the field list.

Hope this helps:) 

[homboy27]

The columns already exist in the Bi file. I just want to bring in another column to the table. How do I do that?

[MasonMA]

If columns already exist in the Power BI dataset, you would just need to modify the Excel query. 

In Excel, Data, Queries & Connections. Find the query that pulls data from the Power BI dataset. In the Navigator/Fields panel, locate and select the new column you want to include. 

This works if the column already exists in the dataset. You can also remove columns here if needed.

[homboy27]

I am talking about adding the column in the excel data set that is linked to the PowerBi file that is on Workspace server. I have already created a table in excel file but not availbale to make changes to table that is already created. 

[MasonMA]

My understanding is once a table is created from a Power BI dataset, its structure is fixed. New columns cannot be added to the table itself, you must either edit the underlying Power Query query or use a PivotTable for dynamic access.

Maybe other users would have better perspectives. 

thanks

[homboy27]

How to edit the underlying Power Query query?

[Aburar_123]

Hi @homboy27 ,

Please follow the below steps,

1. download the dataset file and add new column and publish it again

2. In your excel, go to Data->Refresh All

then, you should be able to see your new column in excel.

[MasonMA]

That was mentioned in my second response:) 

[homboy27]

@MasonMA 

Below was your response - but i am not able to find where to locate the new column 

In Excel, Data, Queries & Connections. Find the query that pulls data from the Power BI dataset. In the Navigator/Fields panel, locate and select the new column you want to include. 

[MasonMA]

Hello @homboy27 

We might have misunderstood each other somewhere in the threads. The connection is actually a live OLAP connection. Unlike imported data, it doesn’t create a query in Power Query, so you won’t see it under Data- Queries & Connections, and you can’t edit it the same way. the only way to add/remove columns is by dragging them in a PivotTable.

For details please refer to from Microsoft. 

https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-excel-power-bi-datasets