Forum Discussion

Yiyi_1989's avatar
Yiyi_1989
Icon for Helper I rankHelper 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 :...
Need Help
rsbin's avatar
rsbin
Icon for Community Champion rankCommunity 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
    Icon for Helper I rankHelper 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
      Icon for Community Champion rankCommunity 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,