Forum Discussion
PowerBI Dataset in Excel
I have build a model in my BI report, with relationship connecting a serval of tables.
I am trying to use Excel connecting to this BI dataset and create a Excel table for users to review regularly.
However, I found that 'relationship' and 'RLS' seems cannot be inherited from BI to Excel.
May I know is there any workaround?
Thanks.
Hi Anonymous
'Manage data model' option in excel will only show the data model when you connect data source in excel. A Data Model is created automatically when you import two or more tables simultaneously from a database. When you import one table, you can select 'add this data to data model'. Refer this article: Advanced Excel - Data Model
In this issue, 'Analyze in excel' just quotes the dataset from power bi service, not as a single data source, this dataset is come from your .pbix file which has included relationships etc. so it will not be used as a single data source to be added to data model in excel.
In other words, the dataset itself has been a model, you can manage it in your power bi desktop not in excel to recreate a model.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , RLS is supported in analyze in excel
"Row-level security (RLS) is supported for Analyze in Excel. RLS is enforced at the data-model level, and is always applied to all users accessing data in the report. Read more about row-level security."
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
refer similar issue and solution - https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a5e803cc-2edc-44e6-a45d-8443bc4bcaee&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
amitchandak thanks for your information.
Besides, is there any way I can keep the relationships between different tables from BI to Excel?Since I found there is no any relationship in the Excel for all those tables anymore.
thanks.
Hi Anonymous
'Manage data model' option in excel will only show the data model when you connect data source in excel. A Data Model is created automatically when you import two or more tables simultaneously from a database. When you import one table, you can select 'add this data to data model'. Refer this article: Advanced Excel - Data Model
In this issue, 'Analyze in excel' just quotes the dataset from power bi service, not as a single data source, this dataset is come from your .pbix file which has included relationships etc. so it will not be used as a single data source to be added to data model in excel.
In other words, the dataset itself has been a model, you can manage it in your power bi desktop not in excel to recreate a model.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
