Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

JSON Keys not importing

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?

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        lbendlin
        Super User
        6 years ago

        are you sure it's ordercount and not orderCount ?

         

        M code is case sensitive in this scenario.