PowerBI导入大量JSON文件遇[Expression.Error]类型转换错误求助

问题背景 #

有一个包含数千个JSON文件的文件夹，导入PowerBI时触发如下错误：

[Expression.Error] We cannot convert a value of type Record to type List.

无法定位具体出错文件，也不清楚解决方法。

正常JSON文件示例 #

[
    {
      "Server": "SERVER",
      "Name": "SHARE NAME",
      "ScopeName": "*",
      "Path": "D:\\PATH\\TO\\SHARE NAME",
      "Description": "",
      "ShareState": 1,
      "AvailabilityType": 0,
      "ShareRights": [
        {
          "AccountName": "BUILTIN\\Administrators",
          "AccessRight": 2,
          "AccessControlType": 0
        }
      ],
      "ShareSddl": "REMOVED",
      "ShareRoot": [
        {
          "Path": "D:\\PATH\\TO\\SHARE",
          "Owner": "BUILTIN\\Administrators",
          "Group": "US\\Domain Users",
          "Access": [
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=BUILTIN\\Administrators}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=CREATOR OWNER}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=NT AUTHORITY\\SYSTEM}",
            "@{FileSystemRights=ReadAndExecute, Synchronize; AccessControlType=Allow; IdentityReference=BUILTIN\\Users}"
          ],
          "Sddl": "REMOVED"
        }
      ],
      "SubDirectories": [
        {
          "Path": "D:\\PATH\\TO\\SHARE\\Report",
          "Owner": "BUILTIN\\Administrators",
          "Group": "US\\Domain Users",
          "Access": [
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=BUILTIN\\Administrators}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=CREATOR OWNER}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=NT AUTHORITY\\SYSTEM}",
            "@{FileSystemRights=ReadAndExecute, Synchronize; AccessControlType=Allow; IdentityReference=BUILTIN\\Users}"
          ],
          "Sddl": "REMOVED"
        }
      ],
      "TotalFileCount": 300,
      "TotalDirectoryCount": 1,
      "TotalFolderSizeBytes": "3,506,449,314 Bytes",
      "TotalFolderSizeInMB": "3,344.01 MB",
      "TotalFolderSizeInGB": "3.27 GB",
      "TreeSizeDirFailed": null,
      "TreeSizeFileFailed": null,
      "TreeErrorHistory": null
    },
    {
      "Server": "SERVER",
      "Name": "Public",
      "ScopeName": "*",
      "Path": "D:\\Public",
      "Description": "",
      "ShareState": 1,
      "AvailabilityType": 0,
      "ShareRights": [
        {
          "AccountName": "Everyone",
          "AccessRight": 0,
          "AccessControlType": 0
        }
      ],
      "ShareSddl": "REMOVED",
      "ShareRoot": [
        {
          "Path": "D:\\Public",
          "Owner": "NT AUTHORITY\\SYSTEM",
          "Group": "NT AUTHORITY\\SYSTEM",
          "Access": [
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=BUILTIN\\Administrators}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=NT AUTHORITY\\SYSTEM}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=CREATOR OWNER}",
            "@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=NT AUTHORITY\\SYSTEM}",
            "@{FileSystemRights=ReadAndExecute, Synchronize; AccessControlType=Allow; IdentityReference=BUILTIN\\Users}"
          ],
          "Sddl": "REMOVED"
        }
      ],
      "SubDirectories": [],
      "TotalFileCount": 0,
      "TotalDirectoryCount": 1,
      "TotalFolderSizeBytes": "1,630,278,865 Bytes",
      "TotalFolderSizeInMB": "1,554.76 MB",
      "TotalFolderSizeInGB": "1.52 GB",
      "TreeSizeDirFailed": null,
      "TreeSizeFileFailed": null,
      "TreeErrorHistory": null
    }
  ]

当前使用的M查询 #

let
    Source = Folder.Files("C:\DATA"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Server", type text}, {"Name", type text}, {"ScopeName", type text}, {"Path", type text}, {"Description", type text}, {"ShareState", Int64.Type}, {"AvailabilityType", Int64.Type}, {"ShareRights", type any}, {"ShareSddl", type text}, {"ShareRoot", type any}, {"SubDirectories", type any}, {"TotalFileCount", Int64.Type}, {"TotalDirectoryCount", Int64.Type}, {"TotalFolderSizeBytes", type text}, {"TotalFolderSizeInMB", type text}, {"TotalFolderSizeInGB", type text}, {"TreeSizeDirFailed", Int64.Type}, {"TreeSizeFileFailed", Int64.Type}, {"TreeErrorHistory", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([ScopeName] = "*")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"SubDirectories", "ScopeName", "Path", "Description", "ShareState", "AvailabilityType", "ShareSddl", "TotalFileCount", "TotalDirectoryCount", "TotalFolderSizeBytes", "TotalFolderSizeInMB", "TotalFolderSizeInGB", "TreeSizeDirFailed", "TreeSizeFileFailed", "TreeErrorHistory"}),
    #"Expanded ShareRoot" = Table.ExpandListColumn(#"Removed Columns1", "ShareRoot"),
    #"Expanded ShareRoot1" = Table.ExpandRecordColumn(#"Expanded ShareRoot", "ShareRoot", {"Path", "Owner", "Group", "Access", "Sddl"}, {"ShareRoot.Path", "ShareRoot.Owner", "ShareRoot.Group", "ShareRoot.Access", "ShareRoot.Sddl"}),
    #"Expanded ShareRights" = Table.ExpandListColumn(#"Expanded ShareRoot1", "ShareRights"),
    #"Expanded ShareRights1" = Table.ExpandRecordColumn(#"Expanded ShareRights", "ShareRights", {"AccountName", "AccessRight", "AccessControlType"}, {"ShareRights.AccountName", "ShareRights.AccessRight", "ShareRights.AccessControlType"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ShareRights1", "Custom", each if Value.Is([ShareRoot.Access], List.Type) then Text.Combine([ShareRoot.Access], "") else Text.Combine({"@{FileSystemRights=" & Number.ToText(Record.Field([ShareRoot.Access], "FileSystemRights")), "; AccessControlType=" & Number.ToText(Record.Field([ShareRoot.Access], "AccessControlType")), "; IdentityReference=" & Record.Field([ShareRoot.Access], "IdentityReference"), "}"})),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([ShareRoot.Path] <> null)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"ShareRoot.Access"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "ShareRoot.Access"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Server", "Name", "ShareRights.AccountName", "ShareRights.AccessRight", "ShareRights.AccessControlType", "ShareRoot.Path", "ShareRoot.Owner", "ShareRoot.Access", "ShareRoot.Group", "ShareRoot.Sddl"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "ID", each [Server] & "-" & [Name]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"ID", "Server", "Name", "ShareRights.AccountName", "ShareRights.AccessRight", "ShareRights.AccessControlType", "ShareRoot.Path", "ShareRoot.Owner", "ShareRoot.Access", "ShareRoot.Group", "ShareRoot.Sddl"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns1","@{","",Replacer.ReplaceText,{"ShareRoot.Access"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","#(cr)#(lf)",Replacer.ReplaceText,{"ShareRoot.Access"})
in
    #"Replaced Value1"

（注：原查询中#"Replaced Value"行存在语法错误，已修正为正确的替换逻辑）

期望输出格式 #

输出为结构化表格，包含ID、Server、Name、共享权限账户、权限值、权限控制类型、共享根路径、所有者、访问权限列表、组、SDDL等字段，其中访问权限列表的每条记录需换行展示。

解决思路 #

1. 定位出错文件 #

• 保留Source.Name列：删除#"Removed Columns"步骤中移除该列的操作，这样出错时可直接关联到对应的文件名。
• 添加错误捕获：修改调用自定义函数的步骤，记录单个文件的转换错误：

  #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each try #"Transform File (2)"([Content]) otherwise [Error = "解析失败", FileName = [Name]]),
  

  后续可筛选包含错误的行，快速定位问题文件。

2. 修复类型转换错误 #

错误核心是部分JSON文件中，ShareRoot或ShareRights字段不是预期的List类型，而是单个Record。需在展开List前统一结构：

• 处理ShareRoot字段：

  #"Fixed ShareRoot Structure" = Table.AddColumn(#"Removed Columns1", "Fixed ShareRoot", each if Value.Is([ShareRoot], Record.Type) then {[ShareRoot]} else [ShareRoot]),
  #"Removed Old ShareRoot" = Table.RemoveColumns(#"Fixed ShareRoot Structure", {"ShareRoot"}),
  #"Renamed Fixed ShareRoot" = Table.RenameColumns(#"Removed Old ShareRoot", {{"Fixed ShareRoot", "ShareRoot"}}),
  #"Expanded ShareRoot" = Table.ExpandListColumn(#"Renamed Fixed ShareRoot", "ShareRoot"),
  

• 处理ShareRights字段：

  #"Fixed ShareRights Structure" = Table.AddColumn(#"Expanded ShareRoot1", "Fixed ShareRights", each if Value.Is([ShareRights], Record.Type) then {[ShareRights]} else [ShareRights]),
  #"Removed Old ShareRights" = Table.RemoveColumns(#"Fixed ShareRights Structure", {"ShareRights"}),
  #"Renamed Fixed ShareRights" = Table.RenameColumns(#"Removed Old ShareRights", {{"Fixed ShareRights", "ShareRights"}}),
  #"Expanded ShareRights" = Table.ExpandListColumn(#"Renamed Fixed ShareRights", "ShareRights"),
  

3. 完善空值与异常处理 #

在#"Added Custom"步骤中增加空值判断，避免空值导致的崩溃：

#"Added Custom" = Table.AddColumn(#"Expanded ShareRights1", "Custom", each 
    if [ShareRoot.Access] = null then ""
    else if Value.Is([ShareRoot.Access], List.Type) then Text.Combine([ShareRoot.Access], "")
    else if Value.Is([ShareRoot.Access], Record.Type) then Text.Combine({"@{FileSystemRights=" & Number.ToText(Record.Field([ShareRoot.Access], "FileSystemRights")), "; AccessControlType=" & Number.ToText(Record.Field([ShareRoot.Access], "AccessControlType")), "; IdentityReference=" & Record.Field([ShareRoot.Access], "IdentityReference"), "}"})
    else Text.From([ShareRoot.Access])
),

4. 验证修正后的查询 #

先使用少量测试文件验证修改后的逻辑，确认无错误后再批量处理所有JSON文件。

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