Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Expanding Records from a Single List

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 CodeCoutry NameValueYear
DEUGermany0.9472019
DEUGermany0.946

2018

BRABrazil0.765

2019

 

Instead of (when I expand only indicator_value):

Country CodeValueYear
DEU0.9472019
DEU0.946

2018

BRA0.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,2019

 

Thank you!

Kind regards,

Lucianna

 

Need Help
  • KNP's avatar
    KNP
    4 years ago

    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"

     

4 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        KNP
        Super User
        4 years ago

        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.