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 ="{ ""syncedIns...
Need Help
Show and Tell
gooranga1's avatar
gooranga1
Power 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
    Power 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