来自Power BI中多家公司的数据的最佳方法
基本上，我有一个大约500x500的大Excel数据集，其中包含来自各种公司的经济信息。
每一行都代表另一家公司，在列中，我们有信息。它的一点点是定性的，例如邮政编码，类型等。但是大部分是定量的。对于每个量化信息，我们都有5年的信息，因此我们每年都有一列，每个信息IE 2019，债务2020等。
因此，我的问题是，哪些是预处理此数据以工作的最佳方法用它以及应该如何完成。要么使用Excel进行预处理，要么在PowerBi上运行脚本，使用查询，SQL，...
目的是拥有一个可以在线访问的报告，用户将键入公司的名称，它将向他们显示仪表板借助该公司的信息（仅是那家公司），因此他们可以通过它进行导航。
每个公司显示的结构和信息都相同，唯一更改的是每个公司拥有的“数字”。因此，必须更改显示哪些数据（使用他们想要的公司的数据）。
它还需要能够向其他公司或总计显示比较数据。
我想从一开始就把它做到，因为然后变化变得复杂。
我考虑过使用定量数据的每个公司进行一个“关系模型”（每年都有一行和每个列的信息点），然后是一个带有定性数据的一般表（排为行每个公司和列提供信息）。但是我不确定。
我知道如何使用Power BI，但我从未将其用于如此大的东西。我想知道如何组织这些数据的方法更好，以及有关如何做的一些信息。
非常感谢大家。
Basically I have a big Excel dataset about 500x500 with economic information from various companies.
Each row is representing a different company and in columns we have the information. A little bit of it is qualitative like ZIP code, type, etc. But most of it is quantitative. For each of the quantitative info, we have info for 5 years, so we have one column for each year and for each information i.e. Debt 2019, Debt 2020, etc.
So my question is which is the best way to preprocess this data to work with it and how should it be done. Either doing the preprocessing with Excel, running a Script on PowerBI, using Query, SQL, ...
The objective is to have a report which will be accessible online and the user will type the name of the company and it will show them the dashboard with the information of that company (only that one), so they can navigate through it.
The structure and which information is shown is the same for each company, the only thing that changes is the "numbers" that each company has. So it has to be possible to change which data is showing (to use the one from the company they want).
It also needs to be able to show comparative data to other groups of companies or to the total.
I want to have it right from the start, because then changes get complicated.
I thought about doing sort of a "relational model" with one "table" for each company with the quantitative data (with one row for each year and each column one info point) and then a general table with the qualitative data (with rows being each company and the columns the info). But I am not really sure.
I know how to use Power BI but I have never used it for something this big. I would like to know which way to organize this data is better and some info on how to do it.
Many thanks to everyone.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，这样做。
一般指导是使用PowerBI中的Power查询将数据转换为星模模型。请参阅了解星形架构及其对Power BI的重要性
因此，这通常会导致一个表格，该表具有每个公司的“维度”数据，一个日期表和带有定量数据的（CompanyId，date）的“事实”表。
Yes, do that.
General guidance is to use Power Query in PowerBI to transform the data into a star schema model. See Understand star schema and the importance for Power BI
So that would typically result in one table that has the "dimension" data for each company, a date table, and a "fact" table at the grain of (CompanyId,Date) with the quantitative data.