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\TOSHARE NAME",
"Description": "",
"ShareState": 1,
"AvailabilityType": 0,
"ShareRights": [
{
"AccountName": "BUILTIN\Administrators",
"AccessRight": 2,
"AccessControlType": 0
}
],
"ShareSddl": "REMOVED",
"ShareRoot": [
{
"Path": "D:\PATH\TOSHARE",
"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\TOSHARE\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!