Power Query: It Takes All Types
22 March 2023
Welcome to our Power Query blog. This week, we extract data created by linked
I have been asked to provide some location data for the manager for the upcoming sales conferences. I realise the area is an odd request, but it will be useful later!
To fill out the rest of the columns, I am going to use ‘Data Types’ on the Data tab:
I select the cities, and click on Geography:
This converts the cities to the Geography Data Type, and a symbol appears next to each city name.
I can use the ‘Insert Data’ menu to select the data for the other columns:
I use the first option ‘Admin Division 2 (County/district/other)’ for Region. I will then use ‘Country/region’ for Country and Area for Area (m2).
I have the data, and I need to extract it to Power Query in order to link it to some other queries.
I select my data, and use ‘From Table/Range’ from the ‘Get & Transform’ section of the ‘Data’ tab:
I accept the defaults, and extract the data:
The area looks great, but nothing else has worked. To investigate, I click on an Error value:
Power Query thinks the cell value is ‘#VALUE’. I discard the query and check what has happened to the Excel data:
The data is fine. Power Query is unable to extract the data with icons next to it. Area (m2) is fine because it has no icon.
I can select my data and right-click. On the right-click menu I choose ‘Data Type’ and ‘Convert to Text’ to remove the Data Type:
The results are not what I was looking for!
Since City is no longer converted to a Location, I get a ‘Field Not Found’ in the other columns.
There are a couple of ways I can fix this. I use CTRL + Z so that City is converted to a Location and choose to copy the data. I then choose to ‘Paste Values’ only by using the icon shown above the ‘Paste Special’ option.
The columns are no longer dependent on City being a Location, but I still have the icons:
I repeat the earlier step by selecting this data and right-clicking. On the right-click menu I choose ‘Data Type’ and ‘Convert to Text’ to remove the Data Type:
Now I have the data in a format that I can extract. I can delete the original table and use my new data:
I extract my data to Power Query:
Come back next time for more ways to use Power Query!