Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
I resolved this by right clicking the column(s) I wanted to expand and selecting "Remove Errors", then clicking Expand again.
@mahoneypat I spoke too soon; when I click Apply in Power Query Editor I get aforementioned error. Any ideas?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |