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.
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!
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.
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
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 |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |