cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgalla010 Frequent Visitor
Frequent Visitor

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. 

image.png

 

Any thoughts are appreciated. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: CSV import is splitting JSON data into their own columns

Hi @tgalla010 ,

 

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,

 

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.

View solution in original post

12 REPLIES 12
Community Support Team
Community Support Team

Re: CSV import is splitting JSON data into their own columns

Hi @tgalla010 ,

 

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.

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.
tgalla010 Frequent Visitor
Frequent Visitor

Re: CSV import is splitting JSON data into their own columns

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

 

Community Support Team
Community Support Team

Re: CSV import is splitting JSON data into their own columns

Hi @tgalla010 ,

 

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.

 

1.PNG

 

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.

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.
tgalla010 Frequent Visitor
Frequent Visitor

Re: CSV import is splitting JSON data into their own columns

Let me see if I can simplify the example just a bit. 

 

  • Source file is a .csv
  • The "details" header or column contains a JSON string. Here is an example of text from the raw .csv
    •  {"shipstation-synced":true,"shipstation-orderId":5287458}
  • This is what PowerBI is doing to the JSON String. It's being "pushed" into the shopify_order_id column. 
 
tgalla010 Frequent Visitor
Frequent Visitor

Re: CSV import is splitting JSON data into their own columns

PowerBi Example.jpg

alena2k Regular Visitor
Regular Visitor

Re: CSV import is splitting JSON data into their own columns

Is it always splitting csv into the same set of undesired columns? If so, it should be possible to merge them back with ," as separator and parse the results normally

e.g.
#"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"details", "shopify_order_id"},Combiner.CombineTextByDelimiter(",""", QuoteStyle.None),"Merged"),
#"Parsed JSON" = Table.TransformColumns(#"Merged Columns",{},Json.Document)
in
#"Parsed JSON"

tgalla010 Frequent Visitor
Frequent Visitor

Re: CSV import is splitting JSON data into their own columns

Unfortunately no it's not being consistent. That's because the JSON isn't consistent and in some instances the following columns have data. I wish I could just do a merge and roll...but that's not the case here. 

alena2k Regular Visitor
Regular Visitor

Re: CSV import is splitting JSON data into their own columns

I see. Can you open it as a text file instead of .csv?

 

let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\sample.csv"), null, null, 1252)}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
#"Changed Type"

Highlighted
tgalla010 Frequent Visitor
Frequent Visitor

Re: CSV import is splitting JSON data into their own columns

I can give that a whirl but I'm confused, is this truely an issue that is seldom seen? I would suspect that anyone who dumps a DB table into a .csv could run into this. Hmmm....

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,304)