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