Forum Discussion

sedrani's avatar
sedrani
Regular Visitor
9 years ago
Solved

Cannot seem to figure out this JSON structure

I'm trying to build a specific table out of a specific JSON API but can't seem to figure it out with this specific structure.

https://api.opencorporates.com/v0.4/jurisdictions

It lists all the jurisdictions they have and I want to get them in excel in the following structure

 

code1 name1 country1 full_name1

code2 name2 country2 full-name2

etc...

 

IF anyone could figure out what's wrong with this code?

let
    Source = Json.Document(Web.Contents("https://api.opencorporates.com/v0.4/jurisdictions")),
    results = Source[results],
    jurisdictions1 = results[jurisdictions],
    #"Converted to Table" = Record.ToTable(jurisdictions1),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"code", "name", "country"})
in
    #"Expanded Value1"
  • sedrani's avatar
    sedrani
    9 years ago

    Never mind

     

    Found it myself

    let
    Source = Json.Document(Web.Contents("https://api.opencorporates.com/v0.4/jurisdictions")),
    results = Source[results],
    jurisdictions1 = results[jurisdictions],
    #"Converted to Table" = Table.FromList(jurisdictions1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"jurisdiction"}, {"Column1.jurisdiction"}),
    #"Expanded Column1.jurisdiction" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.jurisdiction", {"code", "name", "country", "full_name"}, {"Column1.jurisdiction.code", "Column1.jurisdiction.name", "Column1.jurisdiction.country", "Column1.jurisdiction.full_name"})
in
    #"Expanded Column1.jurisdiction"

1 Reply

  • sedrani's avatar
    sedrani
    Regular Visitor
    9 years ago

    Never mind

     

    Found it myself

    let
    Source = Json.Document(Web.Contents("https://api.opencorporates.com/v0.4/jurisdictions")),
    results = Source[results],
    jurisdictions1 = results[jurisdictions],
    #"Converted to Table" = Table.FromList(jurisdictions1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"jurisdiction"}, {"Column1.jurisdiction"}),
    #"Expanded Column1.jurisdiction" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.jurisdiction", {"code", "name", "country", "full_name"}, {"Column1.jurisdiction.code", "Column1.jurisdiction.name", "Column1.jurisdiction.country", "Column1.jurisdiction.full_name"})
in
    #"Expanded Column1.jurisdiction"