Hello!
I have a JSON list which looks like this:
"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
}
]
I want to load this list into a table with the columns id, name, dataType, readOnly, apiKey, timeout, x-original, de-De, de, en and uri, where the columns are null when the respective value is not present in the dataset list entry.
I can expand the list into a table until the point where I need to expand the values column. But this column contains text and record entries, depending on the type in the column dataType. When the value of dataType is "URI" the values column contains only one text. When it is "STRING" the values column contains a record with four additional values. I do not know how to react on these two different types in one column.
I tried to follow the example here and create a custom function to expand the column, but I always end up in errors. Can someone help me how to expand the table correctly?
let CustomExpandTable = (table as table, columnNames as list) =>
let
columnType = (columnName as text) as text => Table.SingleRow(Table.SelectRows(Table.Schema(table), each [Name] = columnName))[TypeName],
expandColumn = (table as table, columnName as text) as table =>
if columnType(columnName) = "Record.Type"
then Table.ExpandRecordColumn(table, columnName, "values", {"x-original", "de-DE", "de", "en"})
else {"values"},
expandedTable = List.Accumulate(columnNames, table, (state, current) => expandColumn(state, current))
in
expandedTable
in
CustomExpandTable
