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": [ { "QuoteNu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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

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

      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"