Programmatically Expand and Manipulate JSON

[ericOnline]

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.?

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

Thank you!

[v-frfei-msft]

Hi @ericOnline,

Please refer to this blog.

Regards,

Frank

[ericOnline]

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!

[ericOnline · Accepted solution]

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!