Forum Discussion

gagan1's avatar
gagan1
Regular Visitor
8 years ago

Lines.FromBinary(Web.Contents . . .

Hi! New user to PowerBi with what is likely a simple question:   I want to extract this data as my source: http://inform.jrc.ec.europa.eu/GNASYSTEM/api001.aspx?workflow=258&request=Global_Peers&iso...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi gagan1,

You can copy and paste this into your Advanced Editor for reference:

let
    Source = Json.Document(Web.Contents("http://inform.jrc.ec.europa.eu/GNASYSTEM/api001.aspx?workflow=258&request=Global_Peers&iso3=SYR")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"version", "request", "WorkflowId", "Iso3", "Country", "IndicatorId", "FullName", "IndicatorScore", "Rank"}, {"version", "request", "WorkflowId", "Iso3", "Country", "IndicatorId", "FullName", "IndicatorScore", "Rank"}),
    #"Removed Top Rows" = Table.Skip(#"Expanded Value1",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Name", "version", "request", "WorkflowId"})
in
    #"Removed Columns"
gagan1's avatar
gagan1
Regular Visitor
8 years ago

Thank you so much!!!!

 

Any thoughts on why changing the url part to "http://inform.jrc.ec.europa.eu/GNASYSTEM/api001.aspx request=GetResultsWFPublished&workflow=258"

 

gets the following

 

DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value="
Position=64

 

Thanks!

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi gagan1

    I can reproduce your problem.

    I test with the suggestion above and the method provided by this article, for your first link, both method can help, but for the second link, it occurs an error when using Get Data->Web and inputing the URL  as below.

     

    It seems the url can't be accessed by anyone,

     

    Best Regards

    Maggie