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 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_codepatient_gidpatient_hipaa_birth_yearpatient_genderhh_income_code
ABC12341900Fnull

 

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.

Show and Tell

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

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

      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.

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

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

      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?

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