Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Convert list of JSON text items to separate columns

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:

TypeTypeGeometry...
FeatureCollection...XXXXX,YYYYY...
FeatureCollection...XXXXX,YYYYY...

 

Thanks!

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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