如何使用 Power Query 将带有嵌套对象的 JSON 导入 Excel？

Question

我需要将 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 中，我使用“数据”>“获取数据”>“从文件”，预览加载后，我单击“转换为表格”，然后得到以下内容：

它将我的两条记录显示为行（很好），但顶级对象只有一列。我需要在 JSON 中包含 3 列，其中包含以下路径：

Column Name: records[0].metadata.colName, Value: records[0].value
Column Name: records[1].metadata.colName, Value: records[1].value
Column Name: records[2].metadata.colName, Value: records[2].value

或这个：

客户编号	工作编号	总销售税
C000087	421444	3.1367982079090906
C000087	421447	68.0271851016993

是否可以？

Answer 1

以下是一种方法：

• 将记录列表扩展为新行，以创建一个表，其中一列为列名，另一列为值
• 使用自定义函数对没有聚合的列名称进行透视

请探索应用步骤以更好地理解算法。

自定义函数
改名fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  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"),

//set data types however you want
//I lazily set them all to text, but you could use text / number / percent
    typeIt = Table.TransformColumnTypes(pivot, List.Transform(Table.ColumnNames(pivot), each {_, type text}))
in
    typeIt

结果