JSON Keys not importing

[Anonymous]

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?

[Anonymous]

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

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

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?

[lbendlin]

are you sure it's ordercount and not orderCount ?

M code is case sensitive in this scenario.

[Anonymous]

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

[lbendlin]

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

[Anonymous]

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.

[lbendlin]

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"

[lbendlin]

you might need to show some of the power query code. Any Unicode characters in the original data that are throwing you off?