一对多表关系

本文介绍了一对多表关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对Power BI非常陌生，所以请耐心等待。因此，假设我有3个XML工作表（我用于Power BI的初始数据源），其中两个是如下的简单表：

I am extremely new to Power BI so please bear with me. So assume I have 3 XML sheets (my initial data sources for Power BI) and 2 of them are as simple tables as follows:

表 b 和 c 代表如上所述的两个XML文件。 文件 a 如下：

The tables b and c represents two of my XML files as stated above. File a is as follows:

Software + | | Microsoft | + | +--->Windows 10 +-->+ | +--->Windows 7 | | Google | + | +-->Chrome +-->+ +-->Mail

或使用XML如下：

<?xml version="1.0"?> <Software Name = "Company Software"> <HeadProduct Name = "Google"> <Product>Chrome</Product> <Product>Mail</Product> </HeadProduct> <HeadProduct Name = "Microsoft"> <Product>Windows 10</Product> <Product>Windows 7</Product> </HeadProduct> </Software>

现在这是我的问题：在我的数据集中，我只有一个 表a ，带有嵌套信息，以及几个表b，c，d等。，带有嵌套信息映射回表a Like

Now here is my problem: In my datasets, I have just one Table a with the nested information and several Tables b, c, d etc. with mappings back to elements in Table a Like

Microsoft ----> Table b Chrome ----> Table c Windows 10 ----> Table d ...

如何在BI中处理和实现这种关系？如果可以的话，我可以更改 File a 的结构。

How do I handle and realize the relationships this in BI? I am okay to change structure of File a if that helps.

谢谢。

推荐答案

我认为这更像是两阶段联接，而不是一对多。

I think this is more of a 'two stage' join rather than one-to-many.

您称为 b， c等的文件似乎都具有相同的架构。我的方法是合并（追加）这些数据集，在每个数据集上添加一列，以将记录标记为需要与任何特定的 Head Product结合起来

The files that you've referred to as "b", "c", etc... all seem to have the same schema. My approach would be to union these datasets (append), adding a column to each that will tag the record as needing to be joined to any particular 'Head Product'

房间文件已附加到单个房间表中，然后在软件表和房间表之间进行合并。

Once the 'room' files have been append into a single 'room' table, then do a merge between your software table and your room table.

在我看来这代表了一个多对多的联接，这是令人担忧的，除非您真正想要的是笛卡尔联接。

It does seem to me that this represents a many to many join, which is concerning unless a Cartesian join is what you are really after.

好的，您要求提供一些示例。因此，我在PowerBI中将您的表格键入了上面。

Ok, you requested some examples. So, I typed your tables above into PowerBI.

第一步是获取表B，并添加一列以帮助其与表A中的主产品相关。所以我编辑查询，转到添加列功能区，添加一个自定义列，如下所示：

The first step is to take table B and add a column that will help it relate to Head Product in Table A. So I edit the query, go to the add column ribbon, add a custom column like so:

对所有房间文件执行此操作。如果您不必手动添加此字段，那将是理想的选择。可能是您可以利用文件夹连接器中的文件名，或者如果您拥有生成这些文件的过程，则可以将其添加到列集中。

Do this for all your 'Room' files. It would be ideal if you didn't have to add this field manually. It may be that you can leverage filenames from the folder connector, or if you own the process that generates these files you might be able to add it to the column set. That's all up to you.

查看查询区域，然后在最后一个查询下单击鼠标右键。将鼠标悬停在菜单项上以遍历新建查询>合并>将查询追加为新项-您将得到以下结果：

Look at the Queries region and right click under the last query. Mouse over menu items to traverse "New Query" > "Combine" > "Append Queries as New" -- you get this result:

现在返回表A。从Home功能区中进行合并查询。

Now go back to table A. Do 'Merge Queries' from the Home ribbon.

展开合并的列。

结果为：