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 an Azure SQL DB that is storing JSON data in one of the columns. Long story on why it is structured this way, but the short answer is "No, I can't change the way the data is stored." This is what the data looks like:
Each variable has a four digit label [e.g. "7x3d"], and then has either a single value [e.g. "12"] or an array of two nested variables, each with their own values or array of values [e.g.{"choice_values":["ABC", "123"], "other_values": ["XKCD"]}.
The challenge I am facing is how to convert the JSON String of data into useable values. My approach is to use some mix of split by delimiter, filter, and or replace characters to split the "form_values" column into two columns (like an unpivoted table) attribute name ["7x3d"] and then values ["12"]. What I cannot figure out is the right mix of steps in Power Query to transform the column.
Two Questions:
Solved! Go to Solution.
Hi @bhmann,
To recognize the JSON string embedded in a SQL table, please add a custom column like this:
Custom=Json.Document([ColumnName])
Then, you can expand the new column to extract your desired values.
Regards,
Yuliana Gu
Hi @bhmann,
To recognize the JSON string embedded in a SQL table, please add a custom column like this:
Custom=Json.Document([ColumnName])
Then, you can expand the new column to extract your desired values.
Regards,
Yuliana Gu
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |