Look up values from disordered JSON objects and place in correct columns

[al358]

In Power BI I have a column containing JSON. Each row contains 3 key:value pairs in the format of 3-digits:4-digits as in the below example

Table 1

{"567":"1259","568":"1535","570":"1264"}
{"393":"9521","392":"5351","394":"4621"}

Each key:value can be used to look up an entry in another table:

Table 2

Key | Value | Entry
-------------------
567 | 1259  | Apple
568 | 1535  | Large
570 | 1264  | Red 
393 | 9521  | Small
392 | 5351  | Pear
394 | 4621  | Green

I want to create 3 new columns to append to Table 1, populated with the entries looked up from table 2 like so:

Fruit | Size  | Colour
----------------------
Apple | Large | Red
Pear  | Small | Green

My go-to solution would be to split the JSON column into 3 by the , delimiter, then merge it with Table 2 by Key. However, as JSON objects are not ordered, the first key in column 1 may represent, say, fruit, whereas the first key in column 2 may represent, say, size. In this illustration, this would result in a size value in the fruit column, which is not the intention:

Fruit | Size  | Colour
----------------------
Apple | Large | Red
Small | Pear  | Green

Thankfully, there is a consistent pattern to the order of the key value and what it represents in my table 1 data. In other words, the key with the lowest value represents fruit, the key with the next highest value represents size and the key with the highest value represents colour.

As such, I thought one solution might be to find a way within Power BI to programmatically take the JSON column and order the JSON content by ascending key value.

Example input:

{"393":"1259","392":"1535","394":"1264"}

Desired output:

{"392": "1535","393":"1259","394": "1264"}

As a complete newbie, I am aware this may not be the best solution to this problem, so I am open to suggestions.

I am also aware that this is down to poor database design, but I'm stuck with it for now!

[al358]

@mahoneypat Thank you, it worked a treat!

However, I have ran into a seperate but related issue (let me know if I should create a seperate post instead of commenting here):

I created 3 columns of keys ("Fruit","Size","Colour") so I can look up these values in table 2. However, I get the below error when I try to expand the column of lookup results for the first key column:

Clicking Go To Error takes me to the custom query you gave me.

Any ideas?

[mahoneypat]

Was this step generated automatically after a merge step?  At that step, did you see a new column with "Table"?  If you click to the right of the word table in one of the rows, you can get a preview of the table below.  What do you see?

Also, what do you mean you added 3 columns with 3 keys?  Those same 3 columns exist in Table 2 and you are merging on 3 columns?

Pat

[al358]

Yes, this step generated automatically after a merge step. There was a new column with "Table".

Here's a sample of the table (the first 3 columns): 

id = the 'Value' element of the JSON key:value we previously discussed. Name contains either "fruit/size/colour" (of course it actually contains something unrelated to fruit, but I used fruit as a simple example to illustrate the point).

Regarding the 3 columns with 3 keys, I expanded your custom query of Fruit/Size/Column records like so (again, not actually fruit!):

I then merge the first expanded (storage_key) column which contains the value of the JSON key:value with the column which contains corresponding values in Table 2.

This got me to a column with tables as per the first image at the beginning of this reply. When I click to expand by name

I get this error:

Does that help?

[al358]

I resolved this by right clicking the column(s) I wanted to expand and selecting "Remove Errors", then clicking Expand again.

[al358]

@mahoneypat I spoke too soon; when I click Apply in Power Query Editor I get aforementioned error. Any ideas?

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvLEYAgDEXRXt6aheSHsRVCJY69C0G3Z+7tHXdArQWuQCX1QFlwblDWDe34CpPAg1FyZOdkV6rZsVPC/H6QBLFVzHG8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JsonData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JsonData", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Result", each let 
thisrecord = Json.Document([JsonData]),
sortedrecord = Record.ReorderFields(thisrecord, List.Sort(Record.FieldNames(thisrecord))),
renamedrecord = Record.RenameFields(sortedrecord, List.Zip({Record.FieldNames(sortedrecord), {"Fruit", "Size", "Color"}}))
in 
renamedrecord),
    #"Expanded Result" = Table.ExpandRecordColumn(#"Added Custom1", "Result", {"Fruit", "Size", "Color"}, {"Fruit", "Size", "Color"})
in
    #"Expanded Result"

The key step is the custom column with this expression. Replace [JsonData] with your actual column name with the Json string.

let
thisrecord = Json.Document([JsonData]),
sortedrecord = Record.ReorderFields(thisrecord, List.Sort(Record.FieldNames(thisrecord))),
renamedrecord = Record.RenameFields(sortedrecord, List.Zip({Record.FieldNames(sortedrecord), {"Fruit", "Size", "Color"}}))
in
renamedrecord

Pat