Forum Discussion

Yiyi_1989's avatar
Yiyi_1989
Helper I
2 years ago

Parse a column with Json data using DAX?

Hello, 

 

Has someone experienced in parse a column with Json data using Dax? I have datasets which are connected to my PowerBI desktop using LiveConnection, so Power Query is not a option for me :(. 

 

My value has such structure as {"id":"12345","name":"Lily","keywords":"null","filters":"[]"} and I want to create a new column with name as column name and the value after name as the column values. 

 

Is it possible to do so using DAX? 

 

Thanks a lot for your help in advance!

 

<3<3<3

 

Brenda

Need Help

3 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    2 years ago

    Yiyi_1989 ,

    DAX has a number of string functions that can be used to parse a text string.

    Here is one method.  I have broken it out into separate columns to better see the logic.
    You can combine it into one if you are comfortable with that.

    FindStart = FIND("name", [JSON] ) + 7  // Find your starting position for the Name

FindEnd = FIND( "keywords", [JSON] ) - 3  // Finds your ending position

Name = MID( [JSON], [FindStart], [FindEnd] - [FindStart] )  // Extracts the Name

    A google search on DAX string functions may lead to other methods.  As I said, this is simply one way.

    Hope this helps.

    Regards,

    • Yiyi_1989's avatar
      Yiyi_1989
      Helper I
      2 years ago

      Thanks a lot! I think your suggestion is inspiring. 

       

      However, I do have an issue regarding the values in [Json]. Those values have basically two different structures:  

       

      One started as {"searchID":"... ... ..." ... } and another one is like the one I mentioned above {"id":"12345","name":"Lily","keywords":"null","filters":"[]"}.

       

      I want to specify in my DAX that the FIND() function only applied to the second structure type. Do you have any idea about what should I do? Thanks a lot!

      • rsbin's avatar
        rsbin
        Community Champion
        2 years ago

        Yiyi_1989 ,

        Use a SWITCH or IF Statement.   In DAX, I much prefer using SWITCH.  So modify the [Name] Column to something like this:

        Name2 = SWITCH(
           TRUE(),
           MID( [JSON], 3, 2 ) = "id", MID( [JSON], [FindStart], [FindEnd] - [FindStart] ),
                                      // this checks for the condition = "id"
           BLANK() ) // if condition <> "id", then blank

        You can replace BLANK() with whatever you want your "else" part to be.

        Hope this resolves this issue.

        Regards,