Forum Discussion
Conditional expand of column
Thank you for your answer. Unfortunately I need the record as columns to get the overview I need. This comes with some limitation, but this is currently okay for me.
I now solved my problem by using two different queries. Each query just expands the needed data for one of the two dataTypes. Afterwards I just combine these queries and get the desired result.
you can use Value.Is to sense that. I would strongly recommend against trying to expand the record into columns. This should go into new rows, with a key/value pair of columns.
BTW, your JSON was malformed (extraneous comma, missing parentheses).
let
Source = Json.Document( "{""datasets"":[
{
""values"": [
{
""x-original"": ""Company A"",
""de-DE"": ""Company A"",
""de"": ""Company A"",
""en"": ""Company A""
}
],
""id"": ""1"",
""name"": ""company"",
""dataType"": ""STRING"",
""readOnly"": false
},
{
""values"": [
""https://link.to.my.website""
],
""apiKey"": ""abc"",
""timeout"": 30000,
""id"": ""2"",
""name"": ""website"",
""dataType"": ""URI"",
""readOnly"": false
}
]}" ),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"values", "id", "name", "dataType", "readOnly", "apiKey", "timeout"}, {"values", "id", "name.1", "dataType", "readOnly", "apiKey", "timeout"}),
#"Expanded values" = Table.ExpandListColumn(#"Expanded Value1", "values"),
#"Replaced Value" = Table.ReplaceValue(#"Expanded values",each [values],each if [dataType]="URI" then #table({"Value"},{{[values]}}) else Record.ToTable([values]),Replacer.ReplaceValue,{"values"}),
#"Expanded values1" = Table.ExpandTableColumn(#"Replaced Value", "values", {"Name", "Value"}, {"Name.2", "Value"})
in
#"Expanded values1"
Thank you for your answer. Unfortunately I need the record as columns to get the overview I need. This comes with some limitation, but this is currently okay for me.
I now solved my problem by using two different queries. Each query just expands the needed data for one of the two dataTypes. Afterwards I just combine these queries and get the desired result.