// One row per reading // Pack: Weather station JSON (Power Query Exercises, https://powerqueryexercises.com/downloads/weather-station-json) // // Source bindings // In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or // Power BI Desktop the first steps of this query do that binding instead: // StationsJson the pack file stations.json -> File.Contents(PackFolder & "stations.json") // Set PackFolder to the folder where you extracted the pack (keep the trailing backslash). // // Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns // 11 rows with columns StationID, Station, At, TempC, Humidity, Lat, Flags. // It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook. // Independent practice material; not affiliated with or endorsed by Microsoft. let PackFolder = "C:\PQE\weather-station-json\", StationsJson = File.Contents(PackFolder & "stations.json"), Source = Json.Document(StationsJson), #"Stations" = Table.FromList(Source[stations], Splitter.SplitByNothing(), {"Station"}), #"Station fields" = Table.ExpandRecordColumn(#"Stations", "Station", {"id", "name", "location", "readings"}, {"StationID", "Station", "location", "readings"}), #"Latitude" = Table.AddColumn(#"Station fields", "Lat", each if [location] = null then null else [location][lat], type number), #"One row per reading" = Table.ExpandListColumn(#"Latitude", "readings"), #"Reading fields" = Table.AddColumn(#"One row per reading", "Flags", each if [readings] = null then null else Text.Combine(Record.FieldOrDefault([readings], "flags", {}), ", "), type text), #"Expanded" = Table.ExpandRecordColumn(#"Reading fields", "readings", {"at", "tempC", "humidity"}, {"At", "TempC", "Humidity"}), #"Removed" = Table.RemoveColumns(#"Expanded", {"location"}) in #"Removed"