JSON importing - expanding list

[cgeraeds]

Hello community,

I have a problem when importing JSON files which I have searched for extensively online but can't find the correct answer to.

I have imported a JSON file which in the Query Editor can be converted to a table. When I expand the records I see the following:

Now these clients (column Name) can have multiple actions (or actie in Dutch). Therefore when I expand the column actie-datum there are a total of 7 rows visible which is corresponding to the number of actions. 

Up until now everything is going steady but now the problems start. For every of the seven rows there should be one value for all the columns which now have lists. But when I expand these too many rows are created. The other option is to extract them with a delimiter. When I do this for example with the column actie-datum-gereed the following shows:

For the first four rows two values per row are inserted but this isn't correct. For example for client 574, his first action has a actie-datum-gereed on 30-03-2019 and for his second action (row 2) this date is 02-04-2019. The problem intensifies when expanding all the other columns. Now is there a solution so that the correct value displays per row and not all of them. This is a fairly simple example but there are also cases in which a client has 30 actions and therefore 30 values in 30 rows. 

Please let me know if anymore information might be helpful.

Thank you in advance.

Chris

[LivioLanzo]

Hi @cgeraeds 

is it possible to share the json file?

[cgeraeds]

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.

[cgeraeds]

Any help would be greatly appreciated. My project can't move on without this problem being solved. 

[LivioLanzo]

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

[cgeraeds]

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. 

[richbenmintz]

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

[cgeraeds]

@richbenmintz 

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.