Forum Discussion
Create new table based on JSON column using DAX
I have the following table (result column is calculated so I can't do it in Power Query):
| result | totalElements |
| {"document":"110","totalElements":"3","list":[{"id":"1","items":"35"},{"id":"5","items":"20"},{"id":"9","items":"70"}]} | 3 |
| {"document":"117","totalElements":"2","list":[{"id":"2","items":"10"},{"id":"4","items":"5"}]} | 2 |
| {"document":"123","totalElements":"2","list":[{"id":"13","items":"20"},{"id":"17","items":"45"}]} | 2 |
| {"document":"131","totalElements":"4","list":[{"id":"23","items":"65"},{"id":"27","items":"20"},{"id":"29","items":"15"},{"id":"32","items":"55"}]} | 4 |
| {"document":"144","totalElements":"5","list":[{"id":"36","items":"5"},{"id":"37","items":"15"},{"id":"39","items":"25"},{"id":"41","items":"55"},{"id":"42","items":"25"}]} | 5 |
The new table, using DAX, should look like this:
| document | totalElements | id | items |
| 110 | 3 | 1 | 35 |
| 110 | 3 | 5 | 20 |
| 110 | 3 | 9 | 70 |
| 117 | 2 | 2 | 10 |
| 117 | 2 | 4 | 5 |
| 123 | 2 | 13 | 20 |
| 123 | 2 | 17 | 45 |
| 131 | 4 | 23 | 65 |
| 131 | 4 | 27 | 20 |
| 131 | 4 | 29 | 15 |
| 131 | 4 | 32 | 55 |
| 144 | 5 | 36 | 5 |
| 144 | 5 | 37 | 15 |
| 144 | 5 | 39 | 25 |
| 144 | 5 | 41 | 55 |
| 144 | 5 | 42 | 25 |
How can I define the new table?
Thanks in advance!
4 Replies
Just curious. What does the data look like before? How did you generate the JSON column? It may be easier to start from the original column(s).
Pat
result comes from a table where it can have multiple different structures, in this calculated table it has been filtered down to only being this string structure.
Maybe I can create the table in Power Query and parse it there?
Doing it in Power Query would be much easier. You could use Json.Document to parse it.
Pat
I know I should probably replace delimiters:
SUBSTITUTE([Element], """,""", "|")To use the PATH functions, but I'm clueless on how to go then row by row and create a whole new table.
