cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericOnline Member
Member

Programmatically Expand and Manipulate JSON

Hello, 

I'm new to PowerBI so please bear with me if I'm missing some obvious functionality.

 

I have 20 JSON files with 20 columns each to import. Rather than click, click, click, to convert to a table and expand/drop/rename the columns, what is the programmatic method for importing JSON, expanding the column headings, dropping some columns, renaming, etc.?

 

powerBILineChart15.pngpowerBILineChart16.png

 

This is all manual for me at the moment and quite time consuming.


Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ericOnline Member
Member

Re: Programmatically Expand and Manipulate JSON

WOW! Just figured out the workflow. Pretty sweet!

 

Caveats:

  • This is good for multiple .json files where the manipulation will be EXACTLY THE SAME
    • Example: All columns will be expanded, dropped, renamed, etc in the same way.

 

For others interested, here is the workflow I used:

 

  1. Click Get Data
  2. Select JSON
  3. Select .json file
  4. Run through your manual manipulation of the JSON file
  5. Once complete, click Home Tab
  6. Click Advanced Editor
  7. The DAX that was being compiled in the background is displayed
  8. Select all and copy
  9. Click New Source
  10. Select JSON
  11. Select another .json file
  12. Click Home Tab
  13. Click Advanced Editor
  14. Paste in the DAX
  15. CRITICAL: Change the file path to the correct file name!
  16. Click ok
  17. BOOM! your .json file has just been manipulated in the same fashion as the initial file

Hope this helps!

3 REPLIES 3
Community Support Team
Community Support Team

Re: Programmatically Expand and Manipulate JSON

Hi @ericOnline,

 

 

Please refer to this blog.

 

Regards,

Frank

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

Re: Programmatically Expand and Manipulate JSON

Hi @v-frfei-msft. Thank you for the resource. I downloaded and viewed the .pbix file in the blog. Looks like I should be focused on the DAX in the Advanced Editor after I transform a JSON file the way I want it. Is this the case?

 

For example, here is the Advanced Editor after I manually transformed a JSON file. I'm assuming I can just reuse this for future JSON transformations as long as the transformations are exactly the same.

 

let
    Source = Json.Document(File.Contents("C:\home\myFile.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"lat", "lon", "temp", "feels_like", "dewpoint", "wind_speed", "wind_gust", "baro_pressure", "visibility", "cloud_cover", "cloud_ceiling", "cloud_base", "humidity", "wind_direction", "sunrise", "sunset", "observation_time"}, {"lat", "lon", "temp", "feels_like", "dewpoint", "wind_speed", "wind_gust", "baro_pressure", "visibility", "cloud_cover", "cloud_ceiling", "cloud_base", "humidity", "wind_direction", "sunrise", "sunset", "observation_time"}),
    #"Expanded temp" = Table.ExpandRecordColumn(#"Expanded Column1", "temp", {"value"}, {"temp.value"}),
    #"Expanded feels_like" = Table.ExpandRecordColumn(#"Expanded temp", "feels_like", {"value"}, {"feels_like.value"}),
    #"Expanded dewpoint" = Table.ExpandRecordColumn(#"Expanded feels_like", "dewpoint", {"value"}, {"dewpoint.value"}),
    #"Expanded wind_speed" = Table.ExpandRecordColumn(#"Expanded dewpoint", "wind_speed", {"value"}, {"wind_speed.value"}),
    #"Expanded wind_gust" = Table.ExpandRecordColumn(#"Expanded wind_speed", "wind_gust", {"value"}, {"wind_gust.value"}),
    #"Expanded baro_pressure" = Table.ExpandRecordColumn(#"Expanded wind_gust", "baro_pressure", {"value"}, {"baro_pressure.value"}),
    #"Expanded visibility" = Table.ExpandRecordColumn(#"Expanded baro_pressure", "visibility", {"value"}, {"visibility.value"}),
    #"Expanded cloud_cover" = Table.ExpandRecordColumn(#"Expanded visibility", "cloud_cover", {"value"}, {"cloud_cover.value"}),
    #"Expanded cloud_ceiling" = Table.ExpandRecordColumn(#"Expanded cloud_cover", "cloud_ceiling", {"value"}, {"cloud_ceiling.value"}),
    #"Expanded cloud_base" = Table.ExpandRecordColumn(#"Expanded cloud_ceiling", "cloud_base", {"value"}, {"cloud_base.value"}),
    #"Expanded humidity" = Table.ExpandRecordColumn(#"Expanded cloud_base", "humidity", {"value"}, {"humidity.value"}),
    #"Expanded wind_direction" = Table.ExpandRecordColumn(#"Expanded humidity", "wind_direction", {"value"}, {"wind_direction.value"}),
    #"Expanded sunrise" = Table.ExpandRecordColumn(#"Expanded wind_direction", "sunrise", {"value"}, {"sunrise.value"}),
    #"Expanded sunset" = Table.ExpandRecordColumn(#"Expanded sunrise", "sunset", {"value"}, {"sunset.value"}),
    #"Expanded observation_time" = Table.ExpandRecordColumn(#"Expanded sunset", "observation_time", {"value"}, {"observation_time.value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded observation_time",{{"observation_time.value", type datetime}, {"sunset.value", type datetime}, {"sunrise.value", type datetime}, {"wind_direction.value", type number}, {"humidity.value", type number}, {"cloud_base.value", type number}, {"cloud_ceiling.value", type number}, {"cloud_cover.value", type number}, {"visibility.value", type number}, {"baro_pressure.value", type number}, {"wind_gust.value", type number}, {"wind_speed.value", type number}, {"dewpoint.value", type number}, {"feels_like.value", type number}, {"temp.value", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"temp.value", "Temp"}, {"lat", "Latitude"}, {"lon", "Longitude"}, {"feels_like.value", "FeelsLike"}, {"dewpoint.value", "DewPoint"}, {"wind_speed.value", "WindSpeed"}, {"wind_gust.value", "WindGust"}, {"baro_pressure.value", "BaroPressure"}, {"visibility.value", "Visibility"}, {"cloud_cover.value", "CloudCover"}, {"cloud_ceiling.value", "CloudCeiling"}, {"cloud_base.value", "CloudBase"}, {"humidity.value", "Humidity"}, {"wind_direction.value", "WindDirection"}, {"sunrise.value", "Sunrise"}, {"sunset.value", "Sunset"}, {"observation_time.value", "ObservedTime"}})
in
    #"Renamed Columns"

Thank you!

 

Highlighted
ericOnline Member
Member

Re: Programmatically Expand and Manipulate JSON

WOW! Just figured out the workflow. Pretty sweet!

 

Caveats:

  • This is good for multiple .json files where the manipulation will be EXACTLY THE SAME
    • Example: All columns will be expanded, dropped, renamed, etc in the same way.

 

For others interested, here is the workflow I used:

 

  1. Click Get Data
  2. Select JSON
  3. Select .json file
  4. Run through your manual manipulation of the JSON file
  5. Once complete, click Home Tab
  6. Click Advanced Editor
  7. The DAX that was being compiled in the background is displayed
  8. Select all and copy
  9. Click New Source
  10. Select JSON
  11. Select another .json file
  12. Click Home Tab
  13. Click Advanced Editor
  14. Paste in the DAX
  15. CRITICAL: Change the file path to the correct file name!
  16. Click ok
  17. BOOM! your .json file has just been manipulated in the same fashion as the initial file

Hope this helps!