Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Change Columns dynamically in a matrix

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...
Show and Tell
DataNinja777's avatar
DataNinja777
Super User
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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"}