Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Trying to bring in some data through a web JSON call. Problem is that not all of the keys import into the JSON query. In particular, the JSON result is:
{
"Listing": [
{
"QuoteNumber": "MHDN20-106176",
"BidDate": "2020-07-02 07.03.00",
"Accounts": "ECP"
},
{
"QuoteNumber": "MHDN20-105822",
"BidDate": "2020-07-02 15.44.00",
"Accounts": "BVR, CTR, ECP"
}
]
}
The "QuoteNumber" and "BidDate" come in just fine, but I can't get "Accounts" to show up. If I take the JSON result and save it as a .JSON file and connect that way, "Accounts" come in fine.
Anyone have any idea why this would be happening?
Hi @Anonymous ,
Try go to query editor>advanced editor,then put the following codes inside:
let
Source = Json.Document(File.Contents(...YourJsonFile...)),
ordercount = Source[ordercount][orderList],
orders = Table.FromRecords(ordercount[orders])
in
orders
Thank you for the help!
The original code that I have been using (which seems to work for a variety of keys, all except "Accounts"):
let
Source = Json.Document(Web.Contents("https://****&BidDate=Today:Today&QuoteStatus=0,2,3,4,5,6,7,9&Listing=Accounts,BidDate,QuoteNumber"))
in
Source
(I abbreviated the link above)
If I add the order count lines:
let
Source = Json.Document(Web.Contents("https://****&BidDate=Today:Today&QuoteStatus=0,2,3,4,5,6,7,9&Listing=Accounts,BidDate,QuoteNumber")),
ordercount = Source[ordercount][orderList],
orders = Table.FromRecords(ordercount[orders])
in
orders
I get the below error:
Expression.Error: The field 'ordercount' of the record wasn't found.
Details:
Listing=[List]
What is the purpose of the "ordercount" and "orders" and do I need to modify those to fit my data?
are you sure it's ordercount and not orderCount ?
M code is case sensitive in this scenario.
Nope,
I am still getting errors. Unfortunately, I have very little experience in M code so far. I attempted to update the proposed code to values that may match my data:
let
Source = Json.Document(Web.Contents("https://****&BidDate=Today:Today&QuoteStatus=0,2,3,4,5,6,7,9&Listing=Accounts,BidDate,QuoteNumber")),
Listing = Source[Listing][quoteList],
quotes = Table.FromRecords(Listing[quoteList])
in
quotes
I am now getting the error:
Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]
Key=quoteList
You use quoteList twice. Not seeing your JSON it is hard to troubleshoot but you probably wanted to do
Listing = Source[Listing],
quotes = Table.FromRecords(Listing[quoteList])
Here is another example of the JSON that is returned from the Http call (this is copied straight out of a browser after making the call):
{
"Listing": [
{
"QuoteNumber": "MHDN20-105289",
"BidDate": "2020-07-06 08.50.00",
"Accounts": "ECP, STB"
},
{
"QuoteNumber": "MHDN20-106189",
"BidDate": "2020-07-06 07.44.00",
"Accounts": "ECP, KGK"
},
{
"QuoteNumber": "MHDN20-106190",
"BidDate": "2020-07-06 08.00.00",
"Accounts": "CTR, ECP, SJC"
},
{
"QuoteNumber": "MHDN20-106127",
"BidDate": "2020-07-06 10.00.00",
"Accounts": "CTR, ECP, SJC"
},
{
"QuoteNumber": "MHDN20-106139",
"BidDate": "2020-07-06 09.00.00",
"Accounts": "CTR, DHP, ECP, KCJ"
},
{
"QuoteNumber": "MHDN20-106171",
"BidDate": "2020-07-06 10.00.00",
"Accounts": "CTR, ECP, SJC"
}
]
}
If I save the as a file, Power BI brings in all keys just fine. If I use the Web.Contents call, the Accounts key is omitted for some reason.
In the above code, I think the quoteListing is a problem, but I am not sure what that would be, or even if I am heading down the right road to get the Accounts data.
Try this code
let
Source = Json.Document(...),
Listing = Source[Listing],
#"Converted to Table" = Table.FromList(Listing, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"QuoteNumber", "BidDate", "Accounts"}, {"QuoteNumber", "BidDate", "Accounts"})
in
#"Expanded Column1"
you might need to show some of the power query code. Any Unicode characters in the original data that are throwing you off?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |