Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
WOW! Just figured out the workflow. Pretty sweet!
Caveats:
For others interested, here is the workflow I used:
Hope this helps!
Hi @ericOnline,
Please refer to this blog.
Regards,
Frank
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!
WOW! Just figured out the workflow. Pretty sweet!
Caveats:
For others interested, here is the workflow I used:
Hope this helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |