Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
cgeraeds
Advocate I
Advocate I

JSON importing - expanding list

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:

image.png

 

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. 

 

image.png

 

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:

image.png

 

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

7 REPLIES 7
LivioLanzo
Solution Sage
Solution Sage

Hi @cgeraeds 

 

is it possible to share the json file?

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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. 

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

 

Capture.PNG

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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. 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.