Expanding Records from a Single List

[Anonymous]

Hello all!

I tried and fail several times. I need help on expanding these records from a list. Kindly see the image below:

as you can see, I have one List with three Records (indicator_value, country_name,indicator_name), but I am able to expand just just one record in this query. I would like to have a single table that merges the columns from the first 2 records. Example:

Record: indicator_value:

Record: country_name:

So, I would like to have a final table looking like that:

Country Code	Coutry Name	Value	Year
DEU	Germany	0.947	2019
DEU	Germany	0.946	2018
BRA	Brazil	0.765	2019

Instead of (when I expand only indicator_value):

Country Code	Value	Year
DEU	0.947	2019
DEU	0.946	2018
BRA	0.765	2019

Here is the API from I am getting the data:

http://ec2-54-174-131-205.compute-1.amazonaws.com/API/HDRO_API.php/indicator_id=137506/year=2018,201...

Thank you!

Kind regards,

Lucianna

[KNP · Accepted solution]

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"

[Anonymous]

@KNP that's incredible! Thank you so much!!

I have a question about this part of the M code. Could you please explain what it is doing? The "empty" symbol. 

Thank you!

[KNP]

So the #"Converted to Table" is obviously referencing the earlier step, and the [indicator_value] the column. The {0} is referring to the first row. Power Query is 0 indexed, so {1} would be the second row. In your case it doesn't matter as there is only one row. If there were more rows, the code would need to be adjusted accordingly. 

I hope this clarifies.

[Anonymous]

Ok! Thank you, @KNP !