Forum Discussion
Creating excel tables from Published PI Reports
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?
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
16 Replies
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:)The columns already exist in the Bi file. I just want to bring in another column to the table. How do I do that?
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.
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?
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?
