JSON list to columns not consistent

[govi]

Hi all,

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

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

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

How can I do this?

Can you help?

Thanks a lot in atvance!

[Stachu]

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

[govi]

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:

Thanks!

govi

[ImkeF]

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) )

[govi]

Hi Imke,

Thanks for helping me out.

The formula results in an error:

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!

[ImkeF]

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.

[govi]

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"