cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ugurgulluev Regular Visitor
Regular Visitor

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

8 REPLIES 8
Arul Member
Member

Re: Converting JSON record to table issue

Hi @ugurgulluev ,

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 

 

ugurgulluev Regular Visitor
Regular Visitor

Re: Converting JSON record to table issue

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

Arul Member
Member

Re: Converting JSON record to table issue

Hi @ugurgulluev ,

You can get some ideas and able to solve this problem with the help of this link,

https://www.c-sharpcorner.com/article/generate-power-bi-reports-from-data-in-json-file/

Thanks,

-Arul

ugurgulluev Regular Visitor
Regular Visitor

Re: Converting JSON record to table issue

Thank you @Arul but this doesn't solve the issue.

 

"Let’s save the table by clicking Close and Apply in the Query Editor. This will enable us to generate the reports from the table, using the available templates."

 

This doesn't happen for me. This is the exact issue.

 

Best regards,

Ugur

ugurgulluev Regular Visitor
Regular Visitor

Re: Converting JSON record to table issue

Any ideas all?

 

Thanks,

Ugur

gooranga1 Senior Member
Senior Member

Re: Converting JSON record to table issue

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"

 

 

ugurgulluev Regular Visitor
Regular Visitor

Re: Converting JSON record to table issue

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 Senior Member
Senior Member

Re: Converting JSON record to table issue

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,617)