Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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"
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"
Works like a charm , thank you !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |