Forum Discussion
Creating excel tables from Published PI Reports
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
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.
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.
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