Forum Discussion
Helper IChanging the order of JSON string to use as columns.
I believe that I start to understand. Not sure yet, if the column names are the names in the json records or the field values, so I created solutions for both. Please check the file enclosed.
Basically, you add a column that collects all data from the current row (except the json field) for the first row of a new table and then you determine the column names from the json in a second column. Finally, you create a new table in each row that assembles the values correctly.
Community ChampionHi Tony_Kuiper ,
this order shouldn't matter, ususally. This works for me if those records sit in an array:
let
Source = "[{ ""application"":""name"" , ""type"":""type 1"",""version"":""version 2"" },#(lf){ ""version"":""version 3"" , ""application"":""name 1"" , ""type"":""type 2"" },#(lf){ ""type"":""type 1"" , ""application"":""name 1"" , ""version"":""version 2"" }]",
#"Parsed JSON" = Json.Document(Source),
#"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"application", "type", "version"}, {"application", "type", "version"})
in
#"Expanded Column1"
Tony_KuiperI'll have to try adding the brackets to see if it will behave as an array because the default data is not like that.
- Tony_Kuiper
I'll give this a try however my data even though it appears as json and in array form doesn't behave like it when trying to parse it...
I have worked around it so far with a create column that just does a LOT of if / then type checks on the columns. It was a lot to create but it works at least. Also a lot faster running than expected, so not a choke point on the (so far) 360k rows of records...