如何在Microsoft Power BI中动态转换自关联位置表数据？

现有如下自关联位置表：

需将其动态转换为如下结构的表格：

请问能否在Microsoft Power BI中实现该转换？

方案一：使用Power Query（M语言）递归展开层级 #

1. 加载数据到Power Query：将原始位置表导入Power Query编辑器。
2. 添加自定义递归函数：在Power Query中新建自定义函数，递归获取每个位置的所有父级信息：

   let
       GetHierarchy = (currentId as number) as record =>
           let
               currentRow = Table.SelectRows(原始表, each [Id] = currentId){0},
               parentId = currentRow[ParentId],
               parentRecord = if parentId = 0 then [Country=null, State=null, District=null, Taluka=null, Governarate=null, Region=null, Sub Region=null, Village=null] else GetHierarchy(parentId),
               updatedRecord = Record.AddField(parentRecord, currentRow[Location Type Name], currentRow[Location Name])
           in
               updatedRecord
   in
       GetHierarchy
   

3. 应用函数并展开字段：添加自定义列调用上述函数，展开该列的所有字段，调整列顺序后将空值替换为NULL。
4. 清理表格：移除中间冗余列，匹配目标表格的列结构。

方案二：使用DAX计算表构建层级 #

1. 创建计算表：基于原始表，为每个层级类型添加计算列，利用DAX的路径函数识别层级关系：

   转换后表 = 
   ADDCOLUMNS (
       原始表,
       "Country", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, 1)),
       "State", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN IF(PATHLENGTH(CurrentPath)>=2, LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, 2))),
       "District", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN IF(PATHLENGTH(CurrentPath)>=3, LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, 3))),
       "Taluka", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN IF(PATHLENGTH(CurrentPath)>=4, LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, 4))),
       "Governarate", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN 
               VAR TypePosition = MAXX(FILTER(ALL(原始表), PATHCONTAINS(CurrentPath, 原始表[Id]) && 原始表[Location Type Name] = "Governarate"), PATHFIND(CurrentPath, 原始表[Id]))
               RETURN IF(NOT ISBLANK(TypePosition), LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, TypePosition))),
       "Region", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN 
               VAR TypePosition = MAXX(FILTER(ALL(原始表), PATHCONTAINS(CurrentPath, 原始表[Id]) && 原始表[Location Type Name] = "Region"), PATHFIND(CurrentPath, 原始表[Id]))
               RETURN IF(NOT ISBLANK(TypePosition), LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, TypePosition))),
       "Sub Region", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN 
               VAR TypePosition = MAXX(FILTER(ALL(原始表), PATHCONTAINS(CurrentPath, 原始表[Id]) && 原始表[Location Type Name] = "Sub Region"), PATHFIND(CurrentPath, 原始表[Id]))
               RETURN IF(NOT ISBLANK(TypePosition), LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, TypePosition))),
       "Village", 
           VAR CurrentPath = PATH(原始表[Id], 原始表[ParentId])
           RETURN 
               VAR TypePosition = MAXX(FILTER(ALL(原始表), PATHCONTAINS(CurrentPath, 原始表[Id]) && 原始表[Location Type Name] = "Village"), PATHFIND(CurrentPath, 原始表[Id]))
               RETURN IF(NOT ISBLANK(TypePosition), LOOKUPVALUE(原始表[Location Name], 原始表[Id], PATHITEM(CurrentPath, TypePosition)))
   )
   

2. 调整格式：将计算表中的空值替换为NULL，调整列顺序匹配目标结构。

两种方案均支持动态更新，原始表数据变化时，转换后的表会自动同步刷新。

内容的提问来源于stack exchange，提问作者parth patel