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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
govi
Helper II
Helper II

JSON list to columns not consistent

Hi all,

 

I have imported a JSON file and I am stuck now.


I now have a column (Column1.properties) with "list" in it:

1.png

 

In the list 18 keys with 18 values should be stored.

 

I tried extracting them like this
AdCustomColumn1 = List.First([Column1.properties])
AddCustomColumn2 = List.First(List.Skip([Column1.properties],1))
AddCustomColumn3 = List.First(List.Skip([Column1.properties],2))
etc

 

But then something goes wrong:
If a record is missing one of the keys, another key, which is in the record, is displayed:

 

I want every key, with their value, in the same column

2.png
How can I do this?

Can you help?

Thanks a lot in atvance!

6 REPLIES 6
Stachu
Community Champion
Community Champion

you say

In the list 18 keys with 18 values should be stored.

I don't think that's the case. If you add new column like this:

List.Count([Column1.properties])

are all the values 18?

If they are then that means the order of the values is not consistent - is there another column with headers for each row?

If they are not all 18, then you haev case like this:

 {"Val1", "Val3"}

instead of this:

{"Val1", null, "Val3"}

if that's the case then you need the header indicator per row. Ideally you could use Record type instead of list, but I am not sure ti works with your JSON

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi Stachu,

 

Thanks for helping me!

 

Not all values are 18.

I know what all keys are named

 

So it is like this:

{"Key1", "Val1", "Key2", "Val2", "Key3", "Val3", etc}

But the next line canbe like this: 

{"Key1", "Val1", "Key3", "Val3", "Key4", "Val4", etc}

Do you know how i can isolate all like this:

2019-08-16_085508.png

Thanks!

govi

Hi @govi ,

you can add a column with this formula:

 

Table.FromRows({List.Alternate([Column1.properties],1,1)}, List.Alternate([Column1.properties],1,1,1) )

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi Imke,

Thanks for helping me out.

 

The formula results in an error:

error1.png

 

The two examples I showed are arbritraty:

So it can be like this:

{"Key1", "Val1", "Key2", "Val2", "Key3", "Val3", etc}

But the next line can be like this: 

{"Key1", "Val1", "Key3", "Val3", "Key4", "Val4", etc}

 

Thanks!

Hi @govi ,

Yes, that's what I tested on with and it worked for me.

Any chance to upload some sample data where this error occurs?

If not, please paste complete M-code from the advanced editor.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi Imke,

 

Thanks again!

I wil past the M-code below.

If this is not sufficient I would like to share the pbix file, but is it possible to do it privately?

 

let
    Source = Web.Contents(
	"https://XXXXXXXXXX",
	[
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",
            #"Accept"="application/json",
			#"x-api-key"="XXXXXXXXXXXX"
		],
		Content=Text.ToBinary("{""query"": ""{questionnaires(id: 136){name,subscriptions{id,isEnabled,user{id,userName,installationDate,institution{id,name}},properties{key,value},events{timestamp,message,messageData}}}}""}")
	]
    ),
    #"JSON" = Json.Document(Source),
    data1 = JSON[data],
    questionnaires1 = data1[questionnaires],
    questionnaires2 = questionnaires1{0},
    subscriptions1 = questionnaires2[subscriptions],
    #"Converted to Table" = Table.FromList(subscriptions1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "isEnabled", "user", "properties", "events"}, {"Column1.id", "Column1.isEnabled", "Column1.user", "Column1.properties", "Column1.events"}),
    #"Expanded Column1.user" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.user", {"id", "userName", "installationDate", "institution"}, {"Column1.user.id", "Column1.user.userName", "Column1.user.installationDate", "Column1.user.institution"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1.user", "Custom", each Table.FromRows({List.Alternate([Column1.properties],1,1)}, List.Alternate([Column1.properties],1,1,1) ))
in
    #"Added Custom"

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors