[Expression.Error] We cannot convert a value of ty...

VTSquirrel · ‎04-23-2024

I've got a folder with thousands of JSON files that I'm trying to import into PowerBI. I'm getting the following error with at least one of the files, but it's difficult to tell which one it is and how to resolve it.

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

This is an example of one of the JSON files:

[
    {
      "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
    }
  ]

And this is the query that I'm using:

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"

And, finally, this is how the output should look:

Any ideas how to resolve this issue? Thanks!

v-jianpeng-msft · ‎04-23-2024

Hi, @VTSquirrel

There may be a problem with your code for M code below:

 #"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"), "}"}))

This part corresponds to the contents of your JSON file:

"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}"
          ],

The ShareRoot.Access field is a single record (object) and not an array of records (list) that your M query expects. To solve this problem, you can modify the M query to handle two cases: ShareRoot.Access is a list of records and a single record:

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

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VTSquirrel · ‎04-23-2024

Hello,

I gave your suggestion a try, however I'm now receiving the following error:

Expression.Error: We cannot convert the value "@{FileSystemRights=F..." to type Record.
Details:
    Value=@{FileSystemRights=FullControl; AccessControlType=Allow; IdentityReference=BUILTIN\Administrators}
    Type=[Type]

This is now the full code:

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 
        let
            accessField = [ShareRoot.Access],
            accessList = if Value.Is(accessField, List.Type) then accessField else {accessField},
            combinedText = List.Transform(accessList, each 
                "@{FileSystemRights=" & Number.ToText(Record.Field(_, "FileSystemRights")) & 
                "; AccessControlType=" & Number.ToText(Record.Field(_, "AccessControlType")) & 
                "; IdentityReference=" & Record.Field(_, "IdentityReference") & "}")
        in
            Text.Combine(combinedText, "")
    ),
    #"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"

Any idea how I can fix this? Thanks in advance for the help. I'm new to M, so still learning.

v-jianpeng-msft · ‎04-23-2024