我正在使用 Power Query 从 json 文件中获取数据。我可以扩展 json 文件中显示为“记录”的元素，一切都很好。但有些元素显示为“列表”。现在这些列表是固定的，因为它们始终包含 3 个元素，不多也不少。所以在我看来它们实际上不需要是列表，但我必须使用它。
JSON 格式如下：
{
"start":0,
"limit":10,
"count":2,
"records":[
{
"id":2024,
"customerNo":"XTEST",
"name":"Abso Clean Industries",
"hold":false,
"status":"A",
"creditType":1,
"creditLimit":"0",
"creditBalance":"0",
"creditApprovedBy":null,
"creditApprovedDate":null,
"openOrders":"0",
"lastInvoiceDate":null,
"specialCode":" ",
"lastYearSales":"0",
"thisYearSales":"0",
"nextYearSales":"0",
"lastYearGP":"0",
"thisYearGP":"0",
"nextYearGP":"0",
"currency":"",
"userDef1":"",
"userDef2":"",
"created":"2020-04-11T17:39:54.901022",
"modified":"2020-04-11T17:39:54.901022",
"invoiceType":"F",
"statementType":"F",
"applyFinanceCharges":false,
"averageDaysToPay":"0",
"lastPaymentAmt":"0",
"lastPaymentDate":null,
"reference":null,
"poRequired":false,
"paymentTerms":{
"code":null,
"description":null
},
"foregroundColor":0,
"backgroundColor":16777215,
"discount":"0",
"receivableAccount":"11210",
"defaultShipTo":"",
"upload":false,
"lastModified":"2020-04-11T17:39:54.887023",
"address":{
"line1":"5705 N Lincoln Ave",
"line2":"",
"line3":"",
"line4":"",
"city":"Chicago",
"postalCode":"60659",
"provState":"IL",
"country":"USA",
"phone":{
"number":"7732759089",
"format":1
},
"fax":{
"number":"",
"format":1
},
"contacts":[
{
"name":"Abram Midkiff"
},
{
"name":"John Smith"
},
{
"name":""
}
],
"territory":{
"code":"",
"description":""
},
"salesperson":{
"code":"",
"name":""
},
"salesTaxes":[
{
"code":1,
"exempt":""
},
{
"code":2,
"exempt":""
},
{
"code":0,
"exempt":""
},
{
"code":0,
"exempt":""
}
],
"shipCode":"",
"shipDescription":"",
"sellLevel":1,
"email":"",
"defaultWarehouse":"VA"
},
"links":{
"self":"https://localhost:10880/api/v2/companies/INSPIRE33/customers/2024"
}
},
{
"id":2025,
"customerNo":"XTEST2",
"name":"ABC Electronics",
"hold":false,
"status":"A",
"creditType":1,
"creditLimit":"0",
"creditBalance":"0",
"creditApprovedBy":null,
"creditApprovedDate":null,
"openOrders":"0",
"lastInvoiceDate":null,
"specialCode":" ",
"lastYearSales":"0",
"thisYearSales":"0",
"nextYearSales":"0",
"lastYearGP":"0",
"thisYearGP":"0",
"nextYearGP":"0",
"currency":"",
"userDef1":"",
"userDef2":"",
"created":"2020-04-11T17:40:58.794062",
"modified":"2020-04-11T17:40:58.794062",
"invoiceType":"F",
"statementType":"F",
"applyFinanceCharges":false,
"averageDaysToPay":"0",
"lastPaymentAmt":"0",
"lastPaymentDate":null,
"reference":null,
"poRequired":false,
"paymentTerms":{
"code":null,
"description":null
},
"foregroundColor":0,
"backgroundColor":16777215,
"discount":"0",
"receivableAccount":"11210",
"defaultShipTo":"",
"upload":false,
"lastModified":"2020-04-11T17:40:58.779279",
"address":{
"line1":"123 Main Street",
"line2":"",
"line3":"",
"line4":"",
"city":"Chicago",
"postalCode":"60659",
"provState":"IL",
"country":"USA",
"phone":{
"number":"7732759089",
"format":1
},
"fax":{
"number":"",
"format":1
},
"contacts":[
{
"name":"Jane Smith"
},
{
"name":"Mary Smith"
},
{
"name":""
}
],
"territory":{
"code":"",
"description":""
},
"salesperson":{
"code":"",
"name":""
},
"salesTaxes":[
{
"code":1,
"exempt":""
},
{
"code":2,
"exempt":""
},
{
"code":0,
"exempt":""
},
{
"code":0,
"exempt":""
}
],
"shipCode":"",
"shipDescription":"",
"sellLevel":1,
"email":"",
"defaultWarehouse":"VA"
},
"links":{
"self":"https://localhost:10880/api/v2/companies/INSPIRE33/customers/2025"
}
}
]
}
这是我生成的 Power Query 代码。
let
Source = Json.Document(File.Contents("C:\Users\peter\OneDrive\Desktop\JsonTest.txt")),
records = Source[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "customerNo", "name", "hold", "status", "creditType", "creditLimit", "creditBalance", "creditApprovedBy", "creditApprovedDate", "openOrders", "lastInvoiceDate", "specialCode", "lastYearSales", "thisYearSales", "nextYearSales", "lastYearGP", "thisYearGP", "nextYearGP", "currency", "userDef1", "userDef2", "created", "modified", "invoiceType", "statementType", "applyFinanceCharges", "averageDaysToPay", "lastPaymentAmt", "lastPaymentDate", "reference", "poRequired", "paymentTerms", "foregroundColor", "backgroundColor", "discount", "receivableAccount", "defaultShipTo", "upload", "lastModified", "address", "links"}, {"Column1.id", "Column1.customerNo", "Column1.name", "Column1.hold", "Column1.status", "Column1.creditType", "Column1.creditLimit", "Column1.creditBalance", "Column1.creditApprovedBy", "Column1.creditApprovedDate", "Column1.openOrders", "Column1.lastInvoiceDate", "Column1.specialCode", "Column1.lastYearSales", "Column1.thisYearSales", "Column1.nextYearSales", "Column1.lastYearGP", "Column1.thisYearGP", "Column1.nextYearGP", "Column1.currency", "Column1.userDef1", "Column1.userDef2", "Column1.created", "Column1.modified", "Column1.invoiceType", "Column1.statementType", "Column1.applyFinanceCharges", "Column1.averageDaysToPay", "Column1.lastPaymentAmt", "Column1.lastPaymentDate", "Column1.reference", "Column1.poRequired", "Column1.paymentTerms", "Column1.foregroundColor", "Column1.backgroundColor", "Column1.discount", "Column1.receivableAccount", "Column1.defaultShipTo", "Column1.upload", "Column1.lastModified", "Column1.address", "Column1.links"}),
#"Expanded Column1.address" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.address", {"line1", "line2", "line3", "line4", "city", "postalCode", "provState", "country", "phone", "fax", "contacts", "territory", "salesperson", "salesTaxes", "shipCode", "shipDescription", "sellLevel", "email", "defaultWarehouse"}, {"Column1.address.line1", "Column1.address.line2", "Column1.address.line3", "Column1.address.line4", "Column1.address.city", "Column1.address.postalCode", "Column1.address.provState", "Column1.address.country", "Column1.address.phone", "Column1.address.fax", "Column1.address.contacts", "Column1.address.territory", "Column1.address.salesperson", "Column1.address.salesTaxes", "Column1.address.shipCode", "Column1.address.shipDescription", "Column1.address.sellLevel", "Column1.address.email", "Column1.address.defaultWarehouse"})
in
#"Expanded Column1.address"
问题出在联系人姓名上。我想要字段 ContactName1、ContactName2、ContactName3。我找不到使用 Excel 用户界面来实现这一点的方法。所以我想我需要编辑 Power Query 代码，但需要这方面的帮助。
彼得
答案1
如果您想要的是联系人姓名，您可以继续扩展。在用户界面中，您可以选择列顶部的双箭头。对于List，您可能希望扩展到新行。
例如：（联系人姓名）：
let
Source = Json.Document(File.Contents("C:\Users\Ron\Desktop\myFile.json")),
records = Source[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"address"}, {"address"}),
#"Expanded address" = Table.ExpandRecordColumn(#"Expanded Column1", "address", {"contacts"}, {"contacts"}),
#"Expanded contacts" = Table.ExpandListColumn(#"Expanded address", "contacts"),
#"Expanded contacts1" = Table.ExpandRecordColumn(#"Expanded contacts", "contacts", {"name"}, {"name"})
in
#"Expanded contacts1"