Power Query - extracting data from nested lists

Question

I have several JSON files that I need to combine in power query. Ever file has the same structure that needs to be pivoted. Here's link to the files

I'm struggling as each file has a column contains a list of records, that prevents power query from pivoting the table.

Here are two screenshots of how it looks like in the query editor, I just need to extract the "landing_points.name" from the list and pivot the whole table into a format like the second screenshot.

How can I do this? any help is greatly appreciated!

Below is the code I used, and got this error message

An error occurred in the ‘Transform File’ query. Expression.Error: We cannot convert a value of type List to type Record. Details: Value=[List] Type=[Type]

Row 93 error reference

let
Source = Folder.Files("C:\Users\ldu\Desktop\Subsea"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type any}, {"Value", type any}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 9), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Name]), "Name", "Value")

in #"Pivoted Column"

Nested lists

Extracted if just pulling one file

You could go to this page and click on "code" to download the file, and the files are in this folder: public/api/v2/cable/ github.com/telegeography/www.submarinecablemap.com — Pilafsky

horseyride horseyride · Accepted Answer · 2021-05-18T12:44:01

Try this

We create an index. Use integer divide to group by filename. Pivot. Expand the list then pull out the name field from the record

let Source = Folder.Files("C:\Users\ldu\Desktop\Subsea"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"RecordToTable" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Query1", each Record.ToTable(Json.Document([Content]))),
#"Removed Other Columns" = Table.SelectColumns(RecordToTable,{"Name", "Transform File from Query1"}),
#"Expanded Transform File from Query1" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform File from Query1", {"Name", "Value"}, {"Name1", "Value"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Transform File from Query1", "Index", 0, 1),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 9), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Name1]), "Name1", "Value"),
#"Expanded landing_points" = Table.ExpandListColumn(#"Pivoted Column", "landing_points"),
#"ExtractName" = Table.TransformColumns(#"Expanded landing_points",{{"landing_points", each   Record.Field(_,"name"), type text}})
in  #"ExtractName"

If that still doesn't work then try this which does the expansion before the pivot

let Source = Folder.Files("C:\Users\ldu\Desktop\Subsea"),
#"RecordToTable" = Table.AddColumn(Source, "Transform File from Query1", each Record.ToTable(Json.Document([Content]))),
#"Removed Other Columns" = Table.SelectColumns(RecordToTable,{"Name", "Transform File from Query1"}),
#"Expanded Transform File from Query1" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform File from Query1", {"Name", "Value"}, {"Name1", "Value"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Transform File from Query1", "Index", 0, 1),

// extract and combine landing points locations
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Name1] = "landing_points"),
#"Expanded Value" = Table.TransformColumns(Table.ExpandListColumn(#"Filtered Rows", "Value"),{{"Value", each Record.Field(_,"name"), type text}}),
Places = Table.Group(#"Expanded Value", {"Name","Index","Name1"}, {{"Value", each Text.Combine([Value],"|"), type text}}),

Recombined = Table.Sort(Table.Combine({Table.SelectRows(#"Added Index", each [Name1] <> "landing_points"), Places }),{{"Index", Order.Ascending}}),

#"Integer-Divided Column" = Table.TransformColumns(Recombined  , {{"Index", each Number.IntegerDivide(_, 9), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Name1]), "Name1", "Value"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Pivoted Column", {{"landing_points", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "landing_points")
in #"Split Column by Delimiter"

Power Query - extracting data from nested lists

1 Answers