Power Query: It Takes All Types
22 March 2023
Welcome to our Power Query blog. This week, we extract data created by linked
types.
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!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1678733168.png/5da4b04852fee5b88688fc9905faac54.jpg)
To fill out the rest of the columns, I am going to use ‘Data Types’ on the Data tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1678733187.png/814d19cbdca9f6154f07c4e5ff07c2e9.jpg)
I select the cities, and click on Geography:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1678733251.png/75517e5cee00300ba0a7388ec86f8ac4.jpg)
This converts the cities to the Geography Data Type, and a symbol appears next to each city name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1678733377.png/3231539c79351d328750daa17d825392.jpg)
I can use the ‘Insert Data’ menu to select the data for the other columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1678733407.png/3b2114aeb360c3f14eb2b13390f34fd0.jpg)
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).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1678733427.png/53ccdcfe4949566e28d594725850f908.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1678733450.png/d1ad8f28bdf17ab81a4d3709a6ed5eb5.jpg)
I accept the defaults, and extract the data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1678733466.png/53cea81e15e0ad481221bbe386c8b8f6.jpg)
The area looks great, but nothing else has worked. To investigate, I click on an Error value:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1678733487.png/71a59d99d18f5e07146b50333851c81c.jpg)
Power Query thinks the cell value is ‘#VALUE’. I discard the query and check what has happened to the Excel data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1678733506.png/fd1f7b4fa109b1ee8ffa4ae94d2a21df.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1678733526.png/7fbe268677e2ed697fbd414c6c8bd79f.jpg)
The results are not what I was looking for!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1678733554.png/7a7a00845a01375cdb04cc70105ef8a9.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1678733579.png/b3387ad1cb9ebd5fa26fc8d0d92fdc78.jpg)
The columns are no longer dependent on City being a Location, but I still have the icons:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1678733604.png/e497a358f3f5e579fd8e13a9fc7fc517.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1678733624.png/5aeaa36745db45065aa3092286ab8ee1.jpg)
Now I have the data in a format that I can extract. I can delete the original table and use my new data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1678733650.png/922c263a7e9fe2ac4d2721c8c2f7f4ff.jpg)
I extract my data to Power Query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1678733679.png/6de21e5ff8b7be3693175a7e3b42830f.jpg)
Come back next time for more ways to use Power Query!