Forum Discussion
JSON importing - expanding list
Hi cgeraeds ,
Looking at your JSON I can see that the the 'actie-lijst' array provides a set of lists with no relationship between the items in each of the lists, the consuming system must know that the data is meant to be displayed/stored as a table and that the list items are sorted correctly. Not sure that Power Query can meet that requirement. Are you able to get JSON that expresses the "actie-lijst" as an array of List Objects like
{
"client_574": {
"naam": "Test1 Client1",
"welzijnscoördinator": "Lorum Ipsum",
"verhaal-algemeen": "Lorum Ipsum",
"bevroren": "ja",
"afweging": "Lorum Ipsum",
"afweging-hermeting": "Lorum Ipsum",
"opmerkingen-algemeen": "Lorum Ipsum",
"overleg-tabel": null,
"bepaald door": "cliënt/inwoner",
"actie-lijst":
[
{
"actie-datum":
"2019-03-13",
"actie-beschrijving":
"Lorum Ipsum",
"actie-datum-gereed":
"2019-03-30",
"actie-wie":
"Lorum Ipsum",
"actie-resultaat":
"Lorum Ipsum",
"actie-vervolg":
"Lorum Ipsum"}
,
{
"actie-datum":
"2019-03-15",
"actie-beschrijving":
"Lorum Ipsum2",
"actie-datum-gereed":
"2019-04-02",
"actie-wie":
"Lorum Ipsum2"
}
]
,
"volgende meting": "11-06-2019",
"werk / daginvulling - actielijn 1": null
}
}Richard
There is a way the query could know which relations it should find. It's always shown like value;value;value and if there are no values in one of the records it shows value;;value. Therefore in this example where there are 6 columns it should know by the position within a cel know what the relationships are:
30-3-2019; ; 4-4-2019
Yes;No;Yes
4-5-2019;1-1-2019;
I could make a dax formula which combines the correct values but that would impede the speed greatly and also require a lot of extra columns.