Getting all records not just the first one dynamically

[Anonymous]

First the data, my api return this:

[

    {

        "result": {

            "1": "Repair",

            "2": "Review",

            "3": "Replace",

            "4": "Lining",

            "5": "Lining",

            "6": "Repair",

            "7": "Repair",

            "8": "Replace",

            "9": "Review",

            "10": "Lining"

        },

        "name": "Planning Quebec city winter 2020"

    },

    {

        "result": {

            "11": "Repair",

            "12": "Review",

            "13": "Repair",

            "14": "Lining",

            "15": "Replace",

            "16": "Repair",

            "17": "Lining",

            "18": "Repair",

            "19": "Repair",

            "20": "Lining"

        },

        "name": "Planning of the city of Munich"

    }

]

my final result that i want dynamically is this:
Name| id | action|
planning of the city of munich | 20 | Lining |
..... etc
Planning of quebec city | 1 | Repair |
...... etc

When i use the desktop to do this though when i expand the Record :

i get something like :
expandRecordPipesAndAction = Table.ExpandRecordColumn(expandGradesRecords, "result", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"}, {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"}),

My goal is to expand the records dynamically so i tryed this approach ( source : https://www.youtube.com/watch?v=wG3J8lO9d5I )

Code:

let

    url = baseUrl,

    json = GetJsonContent(url),

    convertToTable = Table.FromList(json, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Converted to Table" = Table.FromList(converToTable, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"result", "name"}, {"result", "name"}),

    #"Expanded result" = Table.ExpandRecordColumn(#"Expanded Column1", "result", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Expanded Column1", "result"), each _ <> null and _ <> ""))), Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Expanded Column1", "result"), each _ <> null and _ <> ""))))

in

    #"Expanded result"

But it doesnt expand the Second record wich goes 11 to 20 and it looks like that :

This is the hard coded code that works for my final result wanted :
let
url = baseUrl ,
json = GetJsonContent(url),
convertToTable = Table.FromList(json, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandGradesRecords = Table.ExpandRecordColumn(convertToTable, "Column1", {"result", "name"}, {"result", "name"}),
expandRecordPipesAndAction = Table.ExpandRecordColumn(expandGradesRecords, "result", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"}, {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"}),
final_result = Table.UnpivotOtherColumns(expandRecordPipesAndAction, {"name"}, "Pipe Id", "Planning Action")
in
final_result;

My question is how can i expand the second records dynamically and why its not working right now?

[lbendlin · Accepted solution]

Here is a slightly different approach that doesn't require you to know the record item names.

let
Source = Json.Document( "[
{
""result"": {
""1"": ""Repair"",
""2"": ""Review"",
""3"": ""Replace"",
""4"": ""Lining"",
""5"": ""Lining"",
""6"": ""Repair"",
""7"": ""Repair"",
""8"": ""Replace"",
""9"": ""Review"",
""10"": ""Lining""
},
""name"": ""Planning Quebec city winter 2020""
},
{
""result"": {
""11"": ""Repair"",
""12"": ""Review"",
""13"": ""Repair"",
""14"": ""Lining"",
""15"": ""Replace"",
""16"": ""Repair"",
""17"": ""Lining"",
""18"": ""Repair"",
""19"": ""Repair"",
""20"": ""Lining""
},
""name"": ""Planning of the city of Munich""
}
]"),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"result", "name"}, {"result", "name"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1", "R", each Record.FieldNames([result])),
#"Expanded R" = Table.ExpandListColumn(#"Added Custom", "R"),
#"Added Custom1" = Table.AddColumn(#"Expanded R", "S", each Record.FieldOrDefault([result],[R])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"name", "R", "S"})
in
#"Removed Other Columns"

[Anonymous]

Works like a charm , thank you !