Forum Discussion

Christoph_CEDES's avatar
Christoph_CEDES
New Member
9 months ago
Solved

DAX: Convert data type [Record] to columns

Hello,

 

I have a data model which has the data I need stored in a column of type [Record]. Judging by the table layout and knowing the API they mirror, this is a whole JSON (sub-) object which went into a single column instead of unpacking it properly. As I don't have write access to the model and the other department is busy doing their stuff, is there a way to unpack a single column of type [Record] into multiple columns in DAX?

 

I don't need to visualize it, running it in the DAX Query Editor is sufficient for my use case.

 

Here is some information regarding the data structure:

  • Table name: "requestlog"
  • Column name: "sim" (of type [Record])
  • Wanted attribute from the record: "imsi"

 

Bonus question: is there a way to unpack a record into multiple columns (in DAX) without knowing the record's internal structure?

 

I know how to do this in PowerQuery, but I don't have edit permissions on the data model.

 

Thank you very much!

 

  • anilelmastasi's avatar
    anilelmastasi
    9 months ago

    Hello Christoph_CEDES ,

     

    Record fiels can not be accessed in DAX. Only M Language can expand Record files. DAX can only operate on numbers, text, dates etc. This is a known limitation of DAX.

     

    If this solved your issue, please mark it as the accepted solution. āœ…

5 Replies

  • anilelmastasi's avatar
    anilelmastasi
    Super User
    9 months ago

    Hello Christoph_CEDES ,

     

    Record fiels can not be accessed in DAX. Only M Language can expand Record files. DAX can only operate on numbers, text, dates etc. This is a known limitation of DAX.

     

    If this solved your issue, please mark it as the accepted solution. āœ…

  • Smitho's avatar
    Smitho
    Regular Visitor
    8 months ago

    If the column is truly a [Record] type it cannot be accessed  with DAX. If the column is Text with JSON: You can parse with string functions

    • Christoph_CEDES's avatar
      Christoph_CEDES
      New Member
      8 months ago

      This is truly a [Record] type column. It is representing a JSON child object after expanding the first level of JSON.

       

      {
        "field1": "value",
        "field2": "value",
        "field3": {
          "subfield1": "value",
          "subfield2": "value"
        }
      }

       

      After calling the following M code, I end up with a column named [field1] of type record in the model. Like this code:

      let
        response = Web.Contents(...),
        json = JSON.Document(response),
        to_table = Table.FromRecords(json)
      in
        to_table

       Note that the data model author has just missed to to call `

      Table.ExpandRecordColumn(to_table, "field3", ...)`. The question was whether I can rectify this via DAX. Which is not possible.
       
      I just leave this detailed explanation here in case someone else has the same problem.