Power Query: It Takes All Types

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!

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!