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
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!
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
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
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:
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"