cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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?

8 REPLIES 8
Highlighted
Super User II
Super User II

Re: JSON Keys not importing

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

Highlighted
Solution Sage
Solution Sage

Re: JSON Keys not importing

Hi  @MHcconley ,

 

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!
Highlighted
New Member

Re: JSON Keys not importing

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?

Highlighted
Super User II
Super User II

Re: JSON Keys not importing

are you sure it's ordercount and not orderCount ?

 

M code is case sensitive in this scenario.

Highlighted
New Member

Re: JSON Keys not importing

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

Highlighted
Super User II
Super User II

Re: JSON Keys not importing

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

 

 

Highlighted
New Member

Re: JSON Keys not importing

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.

Highlighted
Super User II
Super User II

Re: JSON Keys not importing

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"

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors