Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

CSV import is splitting JSON data into their own columns

Hey everyone, I'm running into an issue...I have a .csv file that is a table dump from a provider. Hence, I have no real control over the formatting of the data...I have to play the hand I'm dealt. So my issue is that the .CSV has a JSON string that PowerBi is parsing on import since the JSON has commas in the text. I don't think I can merge the columns post import and then try to parse with JSON as PowerBI is pushing the JSON text into columns that may have data for other records. 

 

Options = JSON column

Location ID = ID field that JSON is "spilling" into

country_code = ID field that JSON is "spilling" into

 

So merging these columns then trying to do the JSON parse will end up with an error as some of the records have a Location ID. 

 

Any thoughts are appreciated. Thank you!

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, would you please kindly mark your sharing solution as an answer so that it can benefit more users?


    Best regards,

     

13 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Is there a string as json format in each rows of this csv file? Sorry for that, We cannot understand your data model clearly, Could you please provide a sample mockup csv based on fake data? Please don't have any Confidential Information or Real data in it. Please upload your files to One Drive and share the link here.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, there is a JSON string in every row. I'm stumped on this one. Thank you!

       

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Sorry for that, we cannot access your link. Could you please put several json strings based on fake data into the rows of table? Please don't have any Confidential Information or Real data in it. Please delete your shared link if it contain real data.

         

         

        Best regards,

        Community Support Team _ Dong Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I wanted to close this but ended up working around the issue using conditional columns and "contains" function to pull the data out of the field. Thank you.

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, would you please kindly mark your sharing solution as an answer so that it can benefit more users?


      Best regards,

       

  • pbiembedded's avatar
    pbiembedded
    Regular Visitor
    3 years ago

    I am facing same issue. 

    Did anyone find the solution to this ?