I've downloaded an average temperature change dataset formatted like this but with lat/long range across the entire US:
- In the data tab, click on "Queries & Connections" in the "Queries & Connections" group. This brings up a sidebar on the right. Double-click the query you just created, which gets you back to Power Query:
- I duplicated the original query, because we want to manipulate it further (right-click on the query in the left pane, then select "Duplicate"). Name the query something specific. I picked "Unpivoted".
- Select the first column that contains the Latitude values. Then click on the arrow next to "Unpivot Columns" on the "Transform" tab and select "Unpivot Other Columns":
- As a final step, I renamed the resulting columns "Latitude", "Longitude" and "Temperature", then clicked "Close & Load" to put the table onto its own worksheet.
Here is the resulting data set:
---------- ----------- -------------
| Latitude | Longitude | Temperature |
---------- ----------- -------------
| 18.5 | -179.5 | 0 |
| 18.5 | -179 | 0 |
| 18.5 | -178.5 | 0 |
| 18.5 | -178 | 0 |
| 18.5 | -177.5 | 0 |
| 18.5 | -177 | 0 |
| 18.5 | -176.5 | 0 |
| 18.5 | -176 | 0 |
| 18.5 | -175.5 | 0 |
| 18.5 | -175 | 0 |
| 19 | -179.5 | 0 |
| 19 | -179 | 0 |
| 19 | -178.5 | 0 |
| 19 | -178 | 0 |
| 19 | -177.5 | 0 |
| 19 | -177 | 0 |
| 19 | -176.5 | 0 |
| 19 | -176 | 0 |
| 19 | -175.5 | 0 |
| 19 | -175 | 0 |
| 19.5 | -179.5 | 0 |
| 19.5 | -179 | 0 |
| 19.5 | -178.5 | 0 |
| 19.5 | -178 | 0 |
| 19.5 | -177.5 | 0 |
| 19.5 | -177 | 0 |
| 19.5 | -176.5 | 0 |
| 19.5 | -176 | 0 |
| 19.5 | -175.5 | 0 |
| 19.5 | -175 | 0 |
| 20 | -179.5 | 0 |
| 20 | -179 | 1.524704 |
| 20 | -178.5 | 1.489677 |
| 20 | -178 | 1.488556 |
| 20 | -177.5 | 1.485161 |
| 20 | -177 | 0 |
| 20 | -176.5 | 0 |
| 20 | -176 | 0 |
| 20 | -175.5 | 0 |
| 20 | -175 | 0 |
| 20.5 | -179.5 | 0 |
| 20.5 | -179 | 1.484848 |
| 20.5 | -178.5 | 1.484863 |
| 20.5 | -178 | 1.484833 |
| 20.5 | -177.5 | 1.484802 |
| 20.5 | -177 | 1.516785 |
| 20.5 | -176.5 | 1.554611 |
| 20.5 | -176 | 1.5672 |
| 20.5 | -175.5 | 1.567184 |
| 20.5 | -175 | 0 |
| 21 | -179.5 | 0 |
| 21 | -179 | 0 |
| 21 | -178.5 | 0 |
| 21 | -178 | 0 |
| 21 | -177.5 | 0 |
| 21 | -177 | 0 |
| 21 | -176.5 | 0 |
| 21 | -176 | 0 |
| 21 | -175.5 | 0 |
| 21 | -175 | 0 |
| 21.5 | -179.5 | 0 |
| 21.5 | -179 | 0 |
| 21.5 | -178.5 | 0 |
| 21.5 | -178 | 0 |
| 21.5 | -177.5 | 0 |
| 21.5 | -177 | 0 |
| 21.5 | -176.5 | 0 |
| 21.5 | -176 | 0 |
| 21.5 | -175.5 | 0 |
| 21.5 | -175 | 0 |
| 22 | -179.5 | 0 |
| 22 | -179 | 0 |
| 22 | -178.5 | 0 |
| 22 | -178 | 0 |
| 22 | -177.5 | 0 |
| 22 | -177 | 0 |
| 22 | -176.5 | 1.586227 |
| 22 | -176 | 0 |
| 22 | -175.5 | 0 |
| 22 | -175 | 0 |
| 23 | -179.5 | 0 |
| 23 | -179 | 0 |
| 23 | -178.5 | 2.718926 |
| 23 | -178 | 2.743782 |
| 23 | -177.5 | 2.74353 |
| 23 | -177 | 0 |
| 23 | -176.5 | 1.64222 |
| 23 | -176 | 1.661705 |
| 23 | -175.5 | 1.720245 |
| 23 | -175 | 1.755074 |
| 23.5 | -179.5 | 0 |
| 23.5 | -179 | 0 |
| 23.5 | -178.5 | 0 |
| 23.5 | -178 | 3.006203 |
| 23.5 | -177.5 | 3.005981 |
| 23.5 | -177 | 0 |
| 23.5 | -176.5 | 0 |
| 23.5 | -176 | 0 |
| 23.5 | -175.5 | 0 |
| 23.5 | -175 | 1.808762 |
---------- ----------- -------------
And this is the underlying M code:
let
Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Lat/Long", type number}, {"-179.5", type any}, {"-179", type number}, {"-178.5", type number}, {"-178", type number}, {"-177.5", type number}, {"-177", type number}, {"-176.5", type number}, {"-176", type number}, {"-175.5", type number}, {"-175", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Lat/Long"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Lat/Long", "Latitude"}, {"Attribute", "Longitude"}, {"Value", "Temperature"}})
in
#"Renamed Columns"
I hope this is what you are looking for. Please click the check box by this answer to accept it if this solved your problem.