Как импортировать этот JSON с вложенными объектами в Excel с помощью Power Query?

Мне нужно импортировать файл JSON в Excel в табличном формате. Файл JSON содержит вложенные объекты, которые я хочу отобразить как данные столбцов. Я посмотрел несколько видео, прочитал документацию и форумы, но не могу с этим разобраться – полное замешательство.

Вот JSON (только две записи (“строки”))

[{
        "records": [{
                "value": "C000087",
                "metadata": {
                    "colName": "CustID"
                }
            },
            {
                "value": "421444",
                "metadata": {
                    "colName": "JobNumber"
                }
            },
            {
                "value": 3.1367982079090906,
                "metadata": {
                    "colName": "TotalSalesTax"
                }
            }
        ]
    },
    {
        "records": [{
                "value": "C000087",
                "metadata": {
                    "colName": "CustID"
                }
            },
            {
                "value": "421447",
                "metadata": {
                    "colName": "JobNumber"
                }
            },
            {
                "value": 68.0271851016993,
                "metadata": {
                    "colName": "TotalSalesTax"
                }
            }
        ]
    }
]

В Excel я использую Данные > Получить данные > Из файла, предварительный просмотр загружается, и я нажимаю Преобразовать в таблицу, затем получаю это:

Он показывает мои две записи в виде строк (хорошо), но только один столбец для верхнего уровня объекта. Мне нужно, чтобы было 3 столбца с этими путями в JSON:

Название столбца: records[0].metadata.colName, Значение: records[0].value
Название столбца: records[1].metadata.colName, Значение: records[1].value
Название столбца: records[2].metadata.colName, Значение: records[2].value

Или это:

Это возможно?

Вот один из способов:

• Разверните списки записей в новые строки, чтобы создать таблицу с одним столбцом названий столбцов и другим – значениями
• Сводите названия столбцов без агрегации, используя пользовательскую функцию

Пожалуйста, изучите примененные шаги, чтобы лучше понять алгоритм.

Пользовательская функция
переименуйте fnPivotAll

//кредит: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Переименовать:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Основной код

let
    Source = Json.Document(File.Contents("C:\Users\ron\Desktop\json.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"records"}, {"records"}),
    #"Expanded records" = Table.ExpandListColumn(#"Expanded Column1", "records"),
    #"Expanded records1" = Table.ExpandRecordColumn(#"Expanded records", "records", {"value", "metadata"}, {"value", "metadata"}),
    #"Expanded metadata" = Table.ExpandRecordColumn(#"Expanded records1", "metadata", {"colName"}, {"colName"}),
    pivot = fnPivotAll(#"Expanded metadata","colName", "value"),

//установите типы данных как вам нужно
//Я лениво установил их все как текст, но вы можете использовать текст / число / процент
    typeIt = Table.TransformColumnTypes(pivot, List.Transform(Table.ColumnNames(pivot), each {_, type text}))
in
    typeIt

Результаты