power bi自定义地图_如何使用自定义形状图在Power BI中创建地理图

power bi自定义地图

介绍 (Introduction)

This is the third article of a series dedicated to discovering geographic maps in Power BI.

这是致力于在Power BI中发现地理地图的系列文章的第三篇。

In the ToC below the article you can find out references to the previous articles and the project’s goal.

在文章下面的目录中，您可以找到对先前文章和项目目标的引用。

In the previous article, I gave you an overview of what a shape map is, which files it’s made of and I showed you how to use the standard built-in maps available in Power BI. Now it’s time to dig into the way to create your own shape map from the scratch and to display it in Power BI.

在上一篇文章中，我概述了形状图，构成的文件，并向您展示了如何使用Power BI中可用的标准内置图。现在是时候研究从头开始创建自己的形状贴图并将其显示在Power BI中的方法了。

自定义形状图 (Custom shape maps)

In order to use custom shape maps the same rule as built-in applies: there must be a matching field between data and maps attributes. It means that you must know the data definition under the hood of your maps or at least you must be capable of opening and reading a shape map’s data table.

为了使用自定义形状贴图，将使用与内置形状贴图相同的规则：数据和贴图属性之间必须有一个匹配字段。这意味着您必须了解地图背景下的数据定义，或者至少必须能够打开和读取形状地图的数据表。

To demonstrate custom shape maps let’s say we want to display the number of airport passengers in Europe, ranked by country.

假设要演示自定义形状图，我们要显示按国家/地区排名的欧洲机场旅客数量。

First thing we need a shape map. You can download a sample from the bottom of the page; a .zip file called Europe_countries_shp_custom.zip.

首先，我们需要一个形状图。您可以从页面底部下载示例。一个名为Europe_countries_shp_custom.zip的.zip文件。

Unzip the archive and use Excel (or another spreadsheet) to open the file Europe_countries_shp_custom.dbf. It is the table file for the shape that encodes all data description.

解压缩归档文件，然后使用Excel（或其他电子表格）打开文件Europe_countries_shp_custom.dbf 。它是用于编码所有数据描述的形状的表文件。

Columns ISO2 and ISO3 are the most interesting as they contain the ISO code for every country. Rather than the country name, we must take care that in our dataset there’s a field with the same ISO code.

ISO2和ISO3列是最有趣的列，因为它们包含每个国家的ISO代码。除了国家名称以外，我们还必须注意数据集中的字段具有相同的ISO代码。

The .zip archive holds the shapefile definition, but we need a TopoJSON file for using in Power BI.

.zip归档文件包含shapefile定义，但是我们需要一个TopoJSON文件才能在Power BI中使用。

Open a browser and go to www.mapshaper.org an online converter.

打开浏览器，然后访问在线转换器www.mapshaper.org 。

Click on the word select in the page box and pick up the .zip shapefile you want to convert, then click Import. Remember: the whole .zip archive not only the .shp file.

单击页面框中的单词select并选择要转换的.zip shapefile，然后单击Import 。请记住：整个.zip存档不仅是.shp文件。

Mapshaper imports the file and shows you a preview.

Mapshaper会导入文件并显示预览。

Click Export and choose the TopoJSON format. Save the. json file on your hard disk.

单击导出，然后选择TopoJSON格式。保存。硬盘上的json文件。

A good reference for where to find out shape maps, is Christopher Finlan’s blog. He created some geographic maps to use for Mobile Report Publisher. But they can apply to Power BI as well, once converted to TopoJSON.

Christopher Finlan的博客很好地找到了形状图。他创建了一些地理地图以用于Mobile Report Publisher。但是，一旦转换为TopoJSON，它们也可以应用于Power BI。

Alternatively, there are some TopoJSON map already formatted from David Eldersveld. He started creating and collecting some maps for the community. You can download them from his GitHub repository.

另外，还有一些已经由David Eldersveld格式化的TopoJSON映射。他开始为社区创建和收集一些地图。您可以从他的GitHub存储库下载它们。

Ok, up to this point we’ve got a map: now we need some data and we collect them from Wikipedia.

好的，到目前为止，我们已经有了一张地图：现在，我们需要一些数据，并从Wikipedia收集它们。

First step, the list of top 100 busiest airports in Europe.

第一步，列出欧洲最繁忙的100个机场。

Open Power BI then select Get Data > Web. In the window insert the following URL: List of the busiest airports in Europe

打开Power BI，然后选择“ 获取数据”>“ Web” 。在窗口中，输入以下URL：欧洲最繁忙的机场列表

In the preview window select 2016 statistics (provisional) [edit] and then click Load to import the table in Power BI.

在预览窗口中，选择2016年统计信息（临时）[编辑] ，然后单击“ 加载”以将表导入Power BI。

The dataset must be edited before using it; click Edit Queries and do some changes.

在使用数据集之前，必须对其进行编辑；单击编辑查询，然后进行一些更改。

Maybe you want to modify the name; I called mine 2016 statistics. On the left side of the page right-click the dataset and choose Rename.

也许您想修改名称；我称我为2016年统计数据 。在页面左侧，右键单击数据集，然后选择重命名 。

The fields Passengers 2015 and Passengers 2016 are imported as text, but we want them as number, because we are going to do some calculations. Open the dataset. In the upper menu click Transform > Replace Values. In Value To Find write a comma (,) and leave blank Replace With. Click OK.

乘客2015和乘客2016字段是作为文本导入的，但是我们希望它们作为数字，因为我们将进行一些计算。打开数据集。在上方菜单中，单击“ 变换”>“替换值” 。在“ 要查找的值”中，写一个逗号（，），并留空“ 替换为” 。单击确定。

Repeat the operation for both fields Passengers 2015 and 2016. After that, modify the data type. Go to Data Type and select Whole Number. Repeat for both fields.

对“ 乘客2015”和“ 2016”两个字段重复该操作。之后，修改数据类型。转到数据类型，然后选择整数。对两个字段重复此操作。

Select the whole column Rank change 2015-16, right-click > Remove.

选择整列2015-16年排名变化，右键单击> 删除。

Then we must perform some data cleansing. Scroll the dataset up to row 67, Mulhouse. The country is tripled, but the correct one is France. Highlight the cell.

然后，我们必须执行一些数据清理。将数据集滚动到Mulhouse行67。这个国家增加了两倍，但是正确的国家是法国。突出显示单元格。

Transform > Replace Values and replace the string

转换>替换值并替换字符串

“France#(cr)#(lf)#(lf)Switzerland#(cr)#(lf)#(lf)Germany” simply with “France”.

仅用“法国”表示“法国＃（cr）＃（lf）＃（lf）瑞士＃（cr）＃（lf）＃（lf）德国”。

There’s another wrong value in the country from row 86.

该国家/地区第86行的另一个错误值。

Ukraine/Russia must be converted in Ukraine with Replace Values.

乌克兰/俄罗斯必须在乌克兰用替换值进行转换。

Once you’ve completed the transformations Home > Close and Apply, then save the Power BI file. Now we are ready to import the second dataset we need: the ISO Country mapping list.

完成转换后，开始Home> Close and Apply ，然后保存Power BI文件。现在，我们准备导入所需的第二个数据集：ISO国家/地区映射列表。

Select Get Data > Web and insert the following URL:

选择获取数据> Web并插入以下URL：

https://en.wikipedia.org/wiki/ISO_3166-1

https://zh.wikipedia.org/wiki/ISO_3166-1

In the preview window select the table Officially assigned code elements [edit] and click Load.

在预览窗口中，选择表格正式分配的代码元素[edit]并点击加载。

This table needs some modifications, too. Click Edit Queries.

该表也需要修改。单击编辑查询 。

As you can see, the dataset contains a country name and country ISO code. The key codes are Alpha2 and Alpha3 ISO standard definition of 2 or 3 characters for a country.

如您所见，数据集包含国家名称和国家ISO代码。关键代码是一个国家的2个或3个字符的Alpha2和Alpha3 ISO标准定义。

If you wish, change the name of the dataset: I turned mine into CountryCodeISO.

如果愿意，请更改数据集的名称：我将我的代码更改为CountryCodeISO。

Select the whole column Link to ISO 3166-2 subdivision codes right-click > Remove. Do the same for column Independent.

选择整个列链接到ISO 3166-2细分代码 ，然后右键单击> 删除。对独立列执行相同的操作。

Select the column English short name (upper/lower case) right-click > Rename … and change the name into Country.

右键单击英文短名称（大写/小写）列> 重命名...，然后将名称更改为Country 。

There are some countries names that don’t match to other datasets; we need to modify them.

有些国家/地区名称与其他数据集不匹配；我们需要修改它们。

Make sure you selected the Country column; then from the menu bar Transform > Replace Values > Value To Find: “United Kingdom of Great Britain and Northern Ireland” / Replace With: “United Kingdom”.

确保选择了“ 国家/地区”列；然后从菜单栏转换>替换值>查找值 ：“大不列颠及北爱尔兰联合王国” / 替换为 ：“英国”。

Same pattern for “Russian Federation” > to replace with “Russia” and for “Czechia” > to replace with “Czech Republic”.

“俄罗斯联邦”>替换为“俄罗斯”和“捷克语”>替换为“捷克共和国”的模式相同。

Now the CountryCodeISO dataset is ready and can be bind to the 2016 statistics. Menu Home > Merge Queries > Merge Queries as new

现在， CountryCodeISO数据集已准备就绪，可以绑定到2016年统计数据 。菜单主页>合并查询>合并查询为新

Merge two datasets as shown in the picture below: 2016 statistics CountryCodeISO bound by country. Choose Inner Join as kind of join.

合并两个数据集，如下图所示： 2016统计 CountryCodeISO受国家/地区限制。选择内部联接作为联接的类型。

In this way we get a new dataset; call it EU_2016_passengers_stats. If you scroll the dataset to the right you can notice a new column called generically table.

这样我们得到了一个新的数据集。称之为EU_2016_passengers_stats 。如果将数据集向右滚动，您会注意到一个称为通用表的新列。

Click on the double-arrow icon to expand the table and you can see the list of the new columns added from the joined dataset. Select the columns you want to keep and click OK.

单击双箭头图标以展开表，您可以看到从联接的数据集中添加的新列的列表。选择要保留的列，然后单击“确定”。

The lately fields have a NewColumn. prefix in their names. Cut off the prefix by renaming the two columns for Alpha code:

最近的字段有一个NewColumn。 在其名称中添加前缀。通过将两列重命名为Alpha代码来切断前缀：

NewColumn.Alpha-2 code > Alpha-2 code.
NewColumn.Alpha-3 code > Alpha-3 code.
When finished Home > Close & Apply and save the file.

NewColumn.Alpha-2代码> Alpha-2代码。
NewColumn.Alpha-3代码> Alpha-3代码。
完成后，开始>关闭并应用并保存文件。

After long preparation, we are ready to create a custom shape map. Go to the report visualization drag and drop the Shape map visual onto the page. From the dataset EU_2016_passengers_stats put the field Alpha-3 code into the Location box and the Passenger2016 field into Color saturation. Swap to the format menu Shape > Map > + Add Map. In the dialog window select the TopoJSON EU file we’ve created before and press Open. The map shows up on the screen with different color saturation per country, according to the total number of passengers.

经过长时间的准备，我们准备创建自定义形状图。转到报表可视化，将“形状”地图可视化拖放到页面上。从数据集EU_2016_passengers_stats中将字段Alpha-3代码放入“ 位置”框中，将Passenger2016字段放入“ 颜色饱和度”中 。切换到格式菜单形状>地图> +添加地图 。在对话框窗口中，选择我们之前创建的TopoJSON EU文件，然后按Open。根据乘客总数，地图显示在屏幕上，每个国家/地区的颜色饱和度不同。

Stay on the visual’s format menu; under Map select View map keys … A pop-up window opens showing the key values for our TopoJSON map.

停留在视觉效果的格式菜单上；在“地图”下，选择“ 查看地图键 ...”。随即打开一个弹出窗口，显示我们的TopoJSON地图的键值。

As I already mentioned, at least one of these keys must have a corresponding value in the dataset. In my example, the matching field is the three-letters country code. The countries where there isn’t any correspondence are in light-grey. It can depend on:

正如我已经提到的，这些键中的至少一个键在数据集中必须具有相应的值。在我的示例中，匹配字段是三个字母的国家/地区代码。没有对应关系的国家为浅灰色。它可以取决于：

a) there aren’t rows for that value in the dataset;
b) there’s no match among the country code and the dataset.

a）数据集中没有该值的行；
b）国家代码和数据集之间不匹配。

You should be aware of this potential issue and prepare accurately your datasets.

您应该意识到这一潜在问题，并准确地准备数据集。

If you want to change the appearance for non-matching countries, expand the Default Color menu in the Format section for the visual. Switch Show On or Off to display or not the non-matching countries. Color sets the background color and Border Color and Border define the settings for the countries border line.

如果要更改不匹配国家/地区的外观，请在视觉效果的“ 格式”部分中展开“ 默认颜色”菜单。切换显示为 开或关以显示或不显示不匹配的国家。 “颜色”设置背景色，“ 边框颜色”和“ 边框”定义国家边框线的设置。

Remember that the map is fully interactive; it reacts to the selection made in the other visuals of the report. Create a slicer and punt in the Field box the Country from the dataset EU_2016_passengers_stats. Your report should look like this:

请记住，地图是完全互动的；它会对报表其他视觉效果中所做的选择做出React。在数据集EU_2016_passengers_stats的国家/地区的字段框中创建切片器和平底锅。您的报告应如下所示：

Try to select one or more country (by pressing CTRL when selecting a country) and notice how the map adapts based on the selection you made.

尝试选择一个或多个国家（选择一个国家时按CTRL），然后注意地图如何根据您所做的选择进行调整。

As for the built-in maps if you switch On the option Zoom > Selection zoom, the focus moves on the selected country.

对于内置地图，如果在选项缩放>选择缩放上打开，则焦点将移至所选国家/地区。

Now you know how to create and import a custom shape map. What If you want to add more customizations? There are some features that aren’t available in Power BI, but you can get what you want by some workarounds. For complex layouts, you should use an external software for shape file manipulation, such as ArcGIS Desktop or QGIS.

现在，您知道如何创建和导入自定义形状贴图。如果要添加更多自定义内容怎么办？ Power BI中没有某些功能，但是您可以通过一些解决方法来获得所需的功能。对于复杂的布局，应使用外部软件来处理形状文件，例如ArcGIS Desktop或QGIS。

Let’s have a simple example of what I mean. Say you want to add a legend with color ranges and countries grouped by passenger number intervals. It isn’t a feature available in Power BI, but there’s a way to get the result. You can create a new column in the dataset, with a series of nested IF to evaluate for every value in which interval it falls.

让我们举一个简单的例子说明我的意思。假设您要添加带有颜色范围和国家（按旅客人数间隔分组）的图例。它不是Power BI中可用的功能，但是有一种获取结果的方法。您可以在数据集中创建一个带有一系列嵌套IF的新列，以评估其落入间隔的每个值。

First of all, we need to group the original dataset by countries. For every country, we must have a single row with the total number of passengers in 2016. So let’s create a new dataset based on the previous one.

首先，我们需要按国家对原始数据集进行分组。对于每个国家/地区，我们必须在一行中包含2016年的乘客总数。因此，让我们在前一个基础上创建一个新的数据集。

Click Home > Edit Queries > Edit Queries.

单击主页>编辑查询>编辑查询 。

Right-click on dataset EU_2016_passengers_stats > Duplicate. Rename the new dataset: EU_2016_pass_grp.

右键单击数据集EU_2016_passengers_stats >复制 。重命名新数据集： EU_2016_pass_grp 。

Go to Transform > Group By > Advanced. In the Group By Window set the following options:

转到“ 变换”>“分组依据”>“高级” 。在“分组依据”窗口中，设置以下选项：

Group By > Alpha-3 Code then Add grouping > Country. New column name > TotPass2016ByCountry. Operation > Sum. Column > Passengers 2016.

按> Alpha-3代码 分组，然后添加分组>国家 。 新列名称> TotPass2016ByCountry 。 操作>总和。 专栏>乘客2016 。

Click to Home > Close & Apply. Save your file.

单击“ 主页”>“关闭并应用” 。保存文件。

Switch to Data tab. Select the dataset EU_2016_pass_grp then Modeling > New Column.

切换到数据选项卡。选择数据集EU_2016_pass_grp，然后选择建模>新建列 。

In the formula bar insert the following function:

在编辑栏中插入以下功能：

Range = IF(EU_2016_pass_grp[TotPass2016ByCountry] < 10000000;”a) < 10
millions”;IF(AND(EU_2016_pass_grp[TotPass2016ByCountry] >=
10000000;EU_2016_pass_grp[TotPass2016ByCountry] < 50000000);”b) between 10 and 50 million”;
IF(AND(EU_2016_pass_grp[TotPass2016ByCountry] >=
50000000;EU_2016_pass_grp[TotPass2016ByCountry] <100000000);”c) between 50 and 100
millions”;IF(AND(EU_2016_pass_grp[TotPass2016ByCountry] >=
100000000;EU_2016_pass_grp[TotPass2016ByCountry] <= 200000000);”d) between 100 and 200
millions”;”e) > 200 millions”))))

范围= IF（EU_2016_pass_grp [TotPass2016ByCountry] <10000000;” a）<10
百万”; IF（AND（EU_2016_pass_grp [TotPass2016ByCountry]> =
10000000; EU_2016_pass_grp [TotPass2016ByCountry] <50000000）；“ b）10到5000万之间”；
IF（AND（EU_2016_pass_grp [TotPass2016ByCountry]> =
50000000; EU_2016_pass_grp [TotPass2016ByCountry] <100000000）;” c）在50和100之间
百万”; IF（AND（EU_2016_pass_grp [TotPass2016ByCountry]> =
100000000; EU_2016_pass_grp [TotPass2016ByCountry] <= 200000000）;” d）在100到200之间
百万”;” e）> 2亿”））））

Review the dataset. For every row, you’ve a calculated interval in the Range column.

查看数据集。对于每一行，您在“ 范围”列中都有一个计算得出的间隔。

Back to the report visualization. Add a new report page to your PBI file. I called mine Custom shape map legend.

返回报告可视化。将新的报告页面添加到您的PBI文件。我叫我的Custom Shape map图例 。

Create a new shape map. In the Location box put the field Alpha-3 code. In the Legend box drag and drop Range.

创建一个新的形状贴图。在位置框中，输入字段Alpha-3 code 。在“ 图例”框中，拖放“ 范围” 。

Move to Format menu for the visual. Expand Shape > Add map and select again the custom TopoJSON file Europe_countries_shp_custom.json

移动到视觉的格式菜单。展开Shape> Add map，然后再次选择自定义的TopoJSON文件Europe_countries_shp_custom.json

Expand Legend and set the position you prefer, for example Center Right. You can switch title on or off.

展开图例并设置您喜欢的位置，例如“中右” 。您可以打开或关闭标题。

Expand Data Colors and select a custom color for every interval in the range. By default, Power BI plots colors which are not correlated, but probably you want to set different shades of the same color.

展开数据颜色，然后为范围中的每个间隔选择一种自定义颜色。默认情况下，Power BI会绘制不相关的颜色，但是您可能希望设置相同颜色的不同阴影。

Once you’ve done back to Fields menu for the visual and add TotPass2016ByCountry to the Color Saturation box.

完成操作后，返回到“视觉”的“ 字段”菜单，然后将TotPass2016ByCountry添加到“ 颜色饱和度”框中。

The final outcome should look like this:

最终结果应如下所示：

Now you’ve drawn a custom shape map with a custom legend. If you pass the mouse over a country, you can notice a tooltip with code, value, and range.

现在，您已经绘制了带有自定义图例的自定义形状图。如果将鼠标移到某个国家/地区上，您会注意到带有代码，值和范围的工具提示。

The map keeps its interactivity. Try to add a slicer and select one or more countries. Remark that the legend changes according to your selection.

地图保持其交互性。尝试添加切片器并选择一个或多个国家。请注意，图例会根据您的选择而更改。

形状文件的不同格式 (Different formats for shape files)

When I introduced shape files, I mentioned that they are vectors. It means that basically they are made of lines and points. Although a shape file is usually used for showing filled areas, you could draw lines, points, etc. for describing geographic attributes for relevant hotspots.

在介绍形状文件时，我提到它们是矢量。这意味着基本上它们是由线和点组成的。尽管形状文件通常用于显示填充区域，但是您可以绘制线，点等来描述相关热点的地理属性。

Just to give you an example, I prepared a different kind of shape file to import into Power BI; the map of main European cities represented as points. The file EuropeanCities.json is available as a download at the end of the article.

仅举一个例子，我准备了另一种形状文件导入Power BI。以点表示的欧洲主要城市地图。文件EuropeanCities.json可在本文结尾处下载。

Open a new report page. I called mine Custom shape map points.

打开一个新的报告页面。我称呼我为“ 自定义形状贴图点” 。

Add a new shape map. The dataset to refer to is EU_2016_passengers_stats. In the Location box drag and drop the field City served. Optionally, you can add the field Passenger 2016 to the Color saturation box. Go to Format > Shape > Add map and select the map you’ve downloaded before: EuropeanCities.json. If you prefer, change the default color for the points.

添加一个新的形状贴图。要引用的数据集是EU_2016_passengers_stats 。在“ 位置”框中，拖放“服务的城市 ”字段。（可选）您可以将字段Passenger 2016添加到“ 颜色饱和度”框中。转到“ 格式”>“形状”>“添加地图”，然后选择之前下载的地图：EuropeanCities.json。如果愿意，可以更改点的默认颜色。

The new map should look like this one:

新地图应如下所示：

Maybe you can recognize the silhouette of Europe through its main cities. The colored spots are those with a correspondence among the dataset and the map keys; for the gray ones either we don’t have data or the keys don’t match. Try to move the mouse over the point to show up tooltips displaying data.

也许您可以通过其主要城市来了解欧洲的轮廓。彩色斑点是指数据集和地图关键字之间具有对应关系的斑点；对于灰色的，我们没有数据或键不匹配。尝试将鼠标移到该点上以显示显示数据的工具提示。

If you want to know which are the value keys used on the map, click on View map keys, to open the pop-up windows with keys. The cities names in your dataset must be equal to the column NAME in the map, in order to show some data.

如果您想知道哪些是地图上使用的值键，请单击查看地图键 ，以打开带有键的弹出窗口。您的数据集中的城市名称必须等于地图中的NAME列，以便显示一些数据。

Expand Zoom and set Manual zoom to On; you realize that you can move the shape map with the mouse or you can zoom in or out by turning the mouse wheel.

展开缩放并将手动缩放设置为开 ; 您意识到可以用鼠标移动形状图，也可以通过旋转鼠标滚轮来放大或缩小。

This is just a simple example of a different shape map type you can display in Power BI.

这只是可以在Power BI中显示的不同形状贴图类型的简单示例。

I bet you are wondering whether you can overlay two or more layers in a single shape map. Let’s say we would like to bind together the European countries boundaries map with the point cities map.

我敢打赌，您想知道您是否可以在单个形状图中覆盖两个或多个图层。假设我们想将欧洲国家边界地图与点城市地图绑定在一起。

Well, the answer is no. At least not in Power BI. You might use a GIS tool such as QGIS or ArcGIS Desktop to add layers into a map project, merge as a single object and then export it as shape file to be converted in TopoJSON format before using it in Power BI.

好吧，答案是否定的。至少在Power BI中没有。您可以使用QGIS或ArcGIS Desktop等GIS工具将图层添加到地图项目中，合并为单个对象，然后将其导出为形状文件以TopoJSON格式转换，然后在Power BI中使用它。