Forum Discussion

Tony_Kuiper's avatar
Tony_Kuiper
Icon for Helper I rankHelper I
4 years ago
Solved

Changing the order of JSON string to use as columns.

Hi all...   I have a large data set which has a column of potentially usefull tags. The issue is that I'm struggling to get the contents of the json (inside a single column) out into a form that is...
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    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.

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
4 years ago

Hi 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_Kuiper's avatar
Tony_Kuiper
Icon for Helper I rankHelper I
4 years ago

I'll have to try adding the brackets to see if it will behave as an array because the default data is not like that.