Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Converting JSON record to table issue

Hi all,

 

I am having an issue converting a JSON response to a table.

 

Here is my query (URLs and authorization removed for confidentiality):

 

let
    uri1 = "",
    url ="{
      ""syncedInstanceUri"":"&uri1&
    "}",
WebPageSourceFunc = () => Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(url)])),
Ssource = Function.InvokeAfter (WebPageSourceFunc, #duration(0,0,0,1)),
    uri2 = Ssource[uri],
WebPageSourceFunction = () => Json.Document(Web.Contents(""&uri2&"/data?limit=50", [Headers=[Authorization="Basic"]])),
Sasource = Function.InvokeAfter (WebPageSourceFunction, #duration(0,0,0,1)),
    items = Sasource[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ActivityId", "ActivityType", "ActivityDate", "ContactId", "IpAddress", "VisitorId", "VisitorExternalId", "EmailRecipientId", "AssetType", "AssetName", "AssetId", "SubjectLine", "EmailWebLink", "CampaignId", "ExternalId", "DeploymentId", "EmailSendType", "EmailAddress", "ContactIdExt"}, {"Column1.ActivityId", "Column1.ActivityType", "Column1.ActivityDate", "Column1.ContactId", "Column1.IpAddress", "Column1.VisitorId", "Column1.VisitorExternalId", "Column1.EmailRecipientId", "Column1.AssetType", "Column1.AssetName", "Column1.AssetId", "Column1.SubjectLine", "Column1.EmailWebLink", "Column1.CampaignId", "Column1.ExternalId", "Column1.DeploymentId", "Column1.EmailSendType", "Column1.EmailAddress", "Column1.ContactIdExt"})
in
    #"Expanded Column1"

This gives me the exact table that I need within the query, without any issues.

 

However, when I try to apply the query changes, I get the following error:

 

The field "items" of the record wasn't found.

 

Any idea on how to solve the issue?

 

Thanks,

Ugur

Need Help
Show and Tell

8 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    7 years ago

    Hi Anonymous ,

    You can directly get the tables from the edit queries windows itself. After getting the data you can get the data you wanted from the edit queries windows itself.

    Regards,

    -Arul 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Arul,

       

      Thanks for the answer, but I am not sure if I got it correctly.

       

      The query already generates the table I require, no problem there. But when I try to apply the query changes, I got the error I mentioned.

       

      Can you please a little bit more explain?

       

      Thanks,

      Ugur

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Any ideas all?

     

    Thanks,

    Ugur

  • gooranga1's avatar
    gooranga1
    Icon for Power Participant rankPower Participant
    7 years ago

    Have you tried editing the script you have to remove the expanded column line and then go and manually see what columns there are to expand? See if that at least gives you somnething with no errors that you can apply? It might be an error with one of the rows json maybe?

    let
uri1 = "",
url ="{
""syncedInstanceUri"":"&uri1&
"}",
WebPageSourceFunc = () => Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(url)])),
Ssource = Function.InvokeAfter (WebPageSourceFunc, #duration(0,0,0,1)),
uri2 = Ssource[uri],
WebPageSourceFunction = () => Json.Document(Web.Contents(""&uri2&"/data?limit=50", [Headers=[Authorization="Basic"]])),
Sasource = Function.InvokeAfter (WebPageSourceFunction, #duration(0,0,0,1)),
items = Sasource[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),

in
#"Converted to Table"

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi gooranga1,

       

      No luck.. 

       

      I believe it applies the query after converting to table, and then unable to find in this step Source[items], since the query starts reading the code after "IN". 

       

      However, I couldn't stilll figure out how to fix.

       

      Best regards,

      Ugur

      • gooranga1's avatar
        gooranga1
        Icon for Power Participant rankPower Participant
        7 years ago

        hi have you tried this? It sounds like there is no data being returned if the error message says it can't find items?

         

        let
uri1 = "",
url ="{
""syncedInstanceUri"":"&uri1&
"}",
WebPageSourceFunc = () => Json.Document(Web.Contents("", [Headers=[Authorization="Basic", #"Content-type"="application/json"], Content=Text.ToBinary(url)])),
Ssource = Function.InvokeAfter (WebPageSourceFunc, #duration(0,0,0,1)),
uri2 = Ssource[uri],
WebPageSourceFunction = () => Json.Document(Web.Contents(""&uri2&"/data?limit=50", [Headers=[Authorization="Basic"]])),
Sasource = Function.InvokeAfter (WebPageSourceFunction, #duration(0,0,0,1)),
items = Sasource[items]


in
uri1