Excel：仅保存电源枢轴的过滤数据模型并删除查询和连接
美好的一天，
在堆栈溢出上提出了这个问题，但是解决方案主要用于较小的数据集，较大的数据集还没有解决方案（对不起，如果错过了，但是我在这里或其他网站上找不到任何解决方案）。
我正在从公司在线的公司Power BI数据库中的Excel中提取一个文件。这使我可以访问所有枢轴表和措施。
我必须构建一个可以选择供应商的报告，从而将所有数据过滤以仅在带有切片机的Pivot表中包括供应商的数据。这很容易，我可以使用Power Pivot的过滤器或切片机来做到这一点。 需要保存此文件的副本，以便没有连接或查询，并且仅包括供应商的数据，因为它将发送到客户端，并且为了安全性，无法访问其他供应商的数据或整个数据库。原始文件将每周刷新，并选择多个供应商为每个供应商创建报告。
是否有一种方法可以保存过滤的数据模型？
- 在被过滤后不能将枢轴加载到普通表中，然后再次用电源查询将其拉入，因为超过一百万行，桌子不允许。
- 无法将枢轴表的副本或在单独的工作簿中引用，因为它会拉动所有枢轴表和连接，如果我删除连接，则删除了枢轴表，
- 有人建议使用DAX Studio保存到CSV，然后将其拉入一个新工作簿。这不是理想的选择，因为需要每周为多个供应商完成；根据我阅读的DAX Studio CSV，最多只能包含30,000行。
当然，必须有一种在Excel中做到这一点的方法吗？ 任何建议都非常感谢。
Good day,
This question has been asked on Stack Overflow but solutions are mostly for smaller datasets and the bigger ones don't have solutions yet (Sorry if missed it, but I couldn't find any on here or other sites).
I'm extracting a file with Analyse in Excel from the Companies Power BI database Online. This gives me access to all the pivot tables and measures.
I have to build a report where the vendor can be selected, thus filters all the data to only include the vendor's data in pivot tables with slicers. This is easy enough I can do that with a filter or slicer in Power Pivot.
A copy of this file needs to be saved so that there are no connections or queries and only includes the vendor's data, since it will be sent to the client and for security no access to other vendor's data or the whole database are allowed. The original file will be refreshed every week and multiple vendors will be selected to create a report for each.
Is there a way to only save the filtered data model?
- Cannot load the pivot into a normal table after it's been filtered and then pull it in with power query again, because the are over a million rows which Excel tables don't allow.
- Cannot make a copy of the pivot tables or reference them in a separate workbook because that pulls in all pivot tables and connections and if I delete connections it removes the pivot table
- Someone suggestion using DAX studio to save to a csv and then pull it into a new workbook. This is not ideal, since it needs to be done on a weekly basis for multiple vendors; and from what I read DAX studio csv can only contain 30,000 rows max anyway.
Surely there must be a way of doing this in Excel?
Any suggestions are greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的目标是创建一个时间点静态报告。即，当您将其发送出去时，枢轴表将仅显示静态数据，并且不再被操纵。如果是这样，您只需在查询和连接下以及枢轴表设置中删除连接，请确保检查不要打开文件。切片机显然将不再起作用，但是静态数据将保持到位。
如果要发送过滤后的文件，并允许最终用户在其过滤器中操纵枢轴表，而无需看到其他供应商的数据，则需要使用RLS。
Is your aim to create a point in time static report. i.e. when you send it out, the pivot tables will just present static data and can no longer be manipulated. If so, you can just delete the connection under Queries and Connections and in the pivot table settings, make sure to check not to refresh on file open. Slicers will obviously no longer work but the static data will remain in place.
If you want to send the filtered file and allow the end user to manipulate pivot tables within their filter without seeing other vendor's data, you would need to use RLS.