Change Columns dynamically in a matrix

[Anonymous]

Hi Team,

I have a table in my Power Query which has multiple columns, out of which one column is coming up in JSON format which has different comma separated values. An example of the values in JSON column is below-

{"market_code":"ABC","patient_gid":"1234","patient_hipaa_birth_year":1900,"patient_gender":"F","hh_income_code":"null"}

I need to convert those values  in JSON (market_code, patient_gid, patient_hipaa_birth_year, patient_gender, hh_income_code) as columns to that existing table in Power Query and visualize them in a tabular format. Those values in JSON need to be displayed as Columns in a matrix visual, like below-

market_code	patient_gid	patient_hipaa_birth_year	patient_gender	hh_income_code
ABC	1234	1900	F	null

Also the columns should be dynamic as the values in JSON is different for differnt rows.

Please suggest a way by which I can achieve it.

[DataNinja777]

Hi @Anonymous ,

In Power Query, you can dynamically extract JSON fields from a column and expand them into separate columns for visualization in a matrix table. If your dataset has a column containing JSON values like {"market_code":"ABC","patient_gid":"1234","patient_hipaa_birth_year":1900,"patient_gender":"F","hh_income_code":"null"}, you can convert it into structured columns dynamically.

First, load your data into Power Query and ensure the JSON column is properly recognized. To parse the JSON dynamically, you can use the following M code:

let
    Source = <YourTable>,  // Replace with your actual table name
    AddParsedJSON = Table.AddColumn(Source, "Parsed JSON", each Json.Document([JSONColumn])),
    ExpandedColumns = Table.ExpandRecordColumn(AddParsedJSON, "Parsed JSON", Record.FieldNames(AddParsedJSON[Parsed JSON]{0})) 
in
    ExpandedColumns

This script first converts the JSON text into structured records using Json.Document([JSONColumn]). Then, Table.ExpandRecordColumn expands all key-value pairs into separate columns dynamically, ensuring that new JSON fields appearing in different rows are automatically included. The Record.FieldNames(AddParsedJSON[Parsed JSON]{0}) function ensures the expansion covers all available fields without requiring manual selection. Once this transformation is applied, the resulting table will contain separate columns for market_code, patient_gid, patient_hipaa_birth_year, patient_gender, and hh_income_code, which can be used directly in a Power BI matrix visual.

Best regards,

[Anonymous]

Hi @DataNinja777 

Thanks for your time and as I told above I have done these things, but how can you use the new columns in the matrix visual (In the JSON column, fields are different for each row). To explain it better, 

The first row contains the below value in JSON column

{"market_code":"ABC","patient_gid":"1234","patient_hipaa_birth_year":1900,"patient_gender":"F","hh_income_code":"null"}

But second row might now have the same fields, it can have  new fields like below-

{"date_month":"202408","dqm_status":"goodrecords","ingestion_dt_tm":"2024-10-01T02:34:44.000-05:00"}

[TomMartens]

@Anonymous ,

Sorry, i misunderstood the challenge. After you have created columns from the JSON string you need to unpivot the new columns into a key value pair

the result:

Regards,

Tom

[Anonymous]

@TomMartens 

That's exactly I have done, but here comes another challenge, the "Value"Column is created with the data type Text, which is right and if you try to pass this "Value" column in Matrix Visual as Values, it'll just dispplay either First row or the Last row as it's a Text type. 

Do you have any idea to get rid of that and display all corressponding rows?

[TomMartens]

Hey @Anonymous ,

in PowerQuery you can mark the column that contains the JSON string, then select Transform --> Parse --> JSON

This command transforms the JSON string into a Record object, that you then can expand:

Done 😉

Hopefully this provides what you are looking for.

Regards,

Tom

[Anonymous]

Hello @TomMartens 

That's what I am doing currently but the challenge I am facing here is, I am not able to display this in a matrix visual, because the columns are not static and is dynamic.