How to change order of a columns in a dictionary column?

[sinasen]

Hello,

After extracting it from Azure AppInsights, I have a data column with embedded data, just like a python dictionary, which looks like this:

{id: 01, name: ABC, Gender: M}

However, in some rows, the order of the data inside the dictionary is different, like:

row 1: {id: 01, name: ABC, Gender: M}

row 2: {name: ABC, id: 01, Gender: M}

The goal is to eventually split this into multiple columns with a delimiter, so that's why the order of them is important.

How can I change it inside PowerBI so that the order of data in the dictionary in all rows in this column is the same?

[mahoneypat · Accepted solution]

You can just use Json.Document() to parse that input to create a record you can expand to put the data in columns with the same name.

However, I needed to wrap the values in " " to avoid an error (see values in first column).  You can play with the TextEncoding parameter of Json.Document to see if you can make it work w/o the quotes.

Pat