Forum Discussion
JSON importing - expanding list
Hello LivioLanzo ,
Please find attached the file here:
https://www.dropbox.com/s/8stj0vyku8qc8pl/test%20ozo%202.txt?dl=0
Most of the columns when you expand the file are not interesing for this example. You can remove them all except actie-lijst.
Any help would be greatly appreciated. My project can't move on without this problem being solved.
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.
The code can vary depending on the final table you want to construct.
With a bit of Python script I built the below table from your json, you can download the pbix file here:
https://1drv.ms/u/s!AiiWkkwHZChHj2KndK81exhzMN8N
Thank you, LivioLanzo
Looks like a neat piece of coding, but something I can't replicate myself. This is just an example file, which is a segment of the whole table and then I have 5 tables in total. Although this works I'm afraid it isn't a lasting solution for us, do you know of any other way to solve this issue, without the use of Phyton? Or are there no other options as richbenmintz pointed out.
