Expanding Records from a Single List

Hi Anonymous,

Please see attached PBIX. I think it achieves what you're looking for.

Let me know if you have any questions.

Here's the M code if you prefer...

let
  Source = Json.Document(
    Web.Contents(
      "http://ec2-54-174-131-205.compute-1.amazonaws.com/API/HDRO_API.php/indicator_id=137506/year=2018,2019"
    )
  ),
  #"Converted to Table" = Table.FromRecords({Source}),
  country_name = #"Converted to Table"{0}[country_name],
  CountryTable = Record.ToTable(country_name),
  Custom1 = #"Converted to Table"{0}[indicator_value],
  #"Converted to Table1" = Record.ToTable(Custom1),
  #"Expanded Value" = Table.ExpandRecordColumn(
    #"Converted to Table1",
    "Value",
    {"137506"},
    {"137506"}
  ),
  #"Expanded 137506" = Table.ExpandRecordColumn(
    #"Expanded Value",
    "137506",
    {"2018", "2019"},
    {"2018", "2019"}
  ),
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Expanded 137506",
    {"Name"},
    "Year",
    "Value"
  ),
  IndicatorTable = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type number}}),
  Custom2 = Table.NestedJoin(
    CountryTable,
    "Name",
    IndicatorTable,
    "Name",
    "Custom",
    JoinKind.LeftOuter
  ),
  #"Expanded Custom" = Table.ExpandTableColumn(
    Custom2,
    "Custom",
    {"Year", "Value"},
    {"Indicator.Year", "Indicator.Value"}
  )
in
  #"Expanded Custom"