Convert list of JSON text items to separate columns

[Anonymous]

Hi

My data source (CSV) contains a column, the values of which are JSON FeatureCollections as text - i.e. geospatial JSON.

I'd like to extract these values such that I can use PowerBI's built-in JSON tools.

Data example:

List

{'type': 'FeatureCollection', 'features': [{'type': 'Feature', 'properties': {'timestamp': 1000000}, 'geometry': {'type': 'Point', 'coordinates': ['XXXX', -'YYYY']}}, {'type': 'Feature', 'properties': {'timestamp': 2000000}, 'geometry': {'type': 'Point', 'coordinates': ['XXXX', -'YYYY']}}]}

{'type': 'FeatureCollection', 'features': [{'type': 'Feature', 'properties': {'timestamp': 1000000}, 'geometry': {'type': 'Point', 'coordinates': ['XXXX', -'YYYY']}}, {'type': 'Feature', 'properties': {'timestamp': 2000000}, 'geometry': {'type': 'Point', 'coordinates': ['XXXX', -'YYYY']}}]}

I'm realising now this is probably not possible in a "neat" way.  I may end up having to split this column into it's own JSON file, in my code...

Output should look something like:

Type	Type	Geometry	...
FeatureCollection	...	XXXXX,YYYYY	...
FeatureCollection	...	XXXXX,YYYYY	...

Thanks!

[amitchandak]

@Anonymous , I check json on JSON formator , It has two problems, One single quote to replace with double quote

{"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"timestamp": 1000000}, "geometry": {"type": "Point", "coordinates": ["XXXX", "YYYY"]}}, {"type": "Feature", "properties": {"timestamp": 2000000}, "geometry": {"type": "Point", "coordinates": ["XXXX", "YYYY"]}}]}

and - with comma between "XXXX", "YYYY"

I have done replace in power query into a new column. and then right click and Transform -> JSON and was able to work as json.

replace ' with "

replace - with an empty string

my code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo4pNTAwMi+pLEiFsKwUILRbamJJaVGqc35OTmpySWZ+HkRYByqdBpEuhmmKJmQQqvaCovyC1KKSTIQBMP2ZuanFJYm5BTBxQwMwqIXpTE/Nz00tKapE14dhb0B+Zl4Jqq3J+flFKZl5iSVI7oYwIoAAplYXwogEAggrthZoO638ZzQo/BdbqxSrM5oaRlMDPDXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [List = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([List],"-","")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","'","""",Replacer.ReplaceText,{"Custom"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value",{{"Custom", Json.Document}}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Parsed JSON", "Custom", {"type", "features"}, {"Custom.type", "Custom.features"}),
    #"Expanded Custom.features" = Table.ExpandListColumn(#"Expanded Custom", "Custom.features"),
    #"Expanded Custom.features1" = Table.ExpandRecordColumn(#"Expanded Custom.features", "Custom.features", {"type", "properties", "geometry"}, {"Custom.features.type", "Custom.features.properties", "Custom.features.geometry"}),
    #"Expanded Custom.features.properties" = Table.ExpandRecordColumn(#"Expanded Custom.features1", "Custom.features.properties", {"timestamp"}, {"Custom.features.properties.timestamp"}),
    #"Expanded Custom.features.geometry" = Table.ExpandRecordColumn(#"Expanded Custom.features.properties", "Custom.features.geometry", {"type", "coordinates"}, {"Custom.features.geometry.type", "Custom.features.geometry.coordinates"}),
    #"Expanded Custom.features.geometry.coordinates" = Table.ExpandListColumn(#"Expanded Custom.features.geometry", "Custom.features.geometry.coordinates")
in
    #"Expanded Custom.features.geometry.coordinates"

[Anonymous]

Hi @amitchandak . Thanks for your message.

I am not sure this solution will work with my data source. I am working with a CSV file, not a native JSON file.

Here is some better sample data:

**edit** powerbi community is not letting me input a table....I'll type it as text.

column_1 | column_2 | data_column

id-1000 | 100500 | {"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"timestamp": 10000000}, "geometry": {"type": "Point", "coordinates": [16.299051, -3.276112]}}, {"type": "Feature", "properties": {"timestamp": 70000000}, "geometry": {"type": "Point", "coordinates": [16.299051, -3.276112]}}]}

Are you able to copy this table and save it as a CSV file? Will your approach still work?

Thank you

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please refer the content in the following videos to achieve it.

Working with JSON data

Expand JSON Array Column to New Table

Best Regards

[Anonymous]

Hi. That first youtube link looks exactly like what I need. Such a simple solution, yet not an easy thing to search for...

For future viewers: extract json data from column of table.

Much appreciated! 😁