如何克服Power BI,Power View和Power 地图中的地图相关问题

Power BI和Excel中最强大的功能之一就是支持地理空间可视化。在Excel中,我们可以在Power View中使用地图可视化,也可以直接使用Power 地图。如您所知,在Power BI中,有两个内置的可视化来支持地理坐标数据,即Map和Filled map。如果您具有必应地图支持的足够数据,它们可以很好地工作。但是,Power BI和Excel中的地图可视化存在一些问题。在这篇文章中,我将解决一些我自己遇到的问题,并将提供针对这些问题的解决方案。由于Power BI中的“填充地图”和“地图”可视化非常相似,因此我在本文中的重点将放在“地图”可视化上。我的意图不是对Power View和Power 地图进行过多的解释,因此我在本文中的重点将放在Power BI上,而不是其他两个方面。

要求

要试验我在这篇文章中解释的所有内容,您需要具备以下条件:

  • 新的SQL Server示例,WideWorldImportersDW(WWI)。你可以下载 这里
  • The 拉特est version on Power BI桌面 (当前版本为2.35.4399.381 64位(2016年5月))
  • 电子表格 2016或Excel 2013

如果您使用Excel 2016,则需要 开启Power View.

如果您想了解更多信息,请查看此 电子表格 2016中的BI功能.

在Power BI中获取数据

  • 打开Power BI桌面
  • 从SQL Server数据库获取数据
  • 选择Fact.Sales和Dimension.City,然后加载数据

Power BI桌面

Power BI中的地图问题

Power BI中的错误城市

  • 展开“维度城市”表
  • 选择“城市”列,然后将其数据类别更改为城市(数据类别位于功能区的“建模”选项卡上)

Power BI桌面数据类别

  • 将地图视觉效果放入页面
  • 将“城市”放在位置上
  • 在尺寸上加上“不含税总额”

Power BI桌面地图

如您所见,销售分布在不同的国家/地区,但这并不是很安静。

  • 在页面上放置切片器,然后在切片器上放置“国家/地区”
  • 点击“美国”以过滤地图

Power BI桌面切片器

糟糕!这不是安静的权利。发生的是Bing 地图 Engine混淆了城市名称,以便显示美国以外的同名城市,就像新西兰的新普利茅斯一样,但是数据源中的新普利茅斯是美国爱达荷州的新普利茅斯。

解决Power BI中的错误城市问题

解决方案1:在Power BI中将国家和城市串联

我们可以通过在表中添加一个计算列并将“国家”和“城市”列连接起来,从而使Bing 地图引擎找到正确的城市,轻松解决该问题。我们可以通过Power Query或编写DAX表达式来做到这一点。我将解释DAX解决方案,并向您介绍Power Query方案。

  • 右键单击“ 尺寸 市”表,然后单击“新建列”

Power BI桌面计算列

  • 输入以下DAX表达式

国家,城市=‘Dimension 市'[国家/地区]& “, “ & ‘Dimension 市'[City]

  • 点击“国家/地区城市”列,然后从“建模”选项卡将其数据类别更改为“城市”
  • 点击地图
  • 在位置上将“城市”替换为“国家/地区城市”

Power BI桌面地图

嗯!现在看起来好多了。切换到“数据”视图以查看新列的外观。

Power BI桌面计算列

解决方案2:在Power BI中准备位置

我们可以使用地图上的“位置”列来获取更精确的点。查看“位置”列,我们很快发现它包含我们真正需要的纬度和经度信息。我们只需要从字符串中删除“ POINTS(“和”),然后用逗号替换空格即可从“位置”列中获取基于纬度和经度的位置数据。

  • 从功能区的“主页”选项卡中单击“编辑查询”

Power BI桌面编辑查询

  • 打开“维度之城”
  • 右键单击“位置”列,然后单击“替换值”

Power BI桌面 Power查询替换值

  • 在“要查找的值”中输入“ POINT(”,并将“替换为”保留为空。这将从文本中删除“ POINT(”

Power BI桌面 Power查询替换值

  • 右键单击“位置”,然后再次单击“替换值”
  • 这次将“)”替换为空

Power BI桌面 Power查询替换值

  • 最后用逗号替换空格

Power BI桌面 Power查询替换值

  • 这就是我们得到的

Power BI桌面 Power查询替换值

您现在可以单击关闭 &应用。但是,等等,我们可以仅一步完成上述三个步骤吗?
答案是肯定的。您可以添加一个新列,并在下面添加一个表达式,该表达式只需一步即可完成上述三个步骤:
= Text.Replace(Text.Trim(Text.Remove([Location],{“A”..”Z”, “(“, “)”})), ” “, “,”)
  • 现在点击“Close and Apply”

Power BI桌面

  • 点击地图,然后将“美国城市”替换为“位置”列

Power BI桌面地图

糟糕!现在怎么了?该位置旨在在地图上显示更精确的点,但是,为什么它显示南极洲的大多数地方?认真吗

这是下一个问题出现的时间。

好,请继续阅读以找出为什么Map显示错误数据。

将SQL Server“地理位置”数据类型转换为Power Query文本时的Power Query问题

让我们看一下到目前为止我们所经历的整个过程,以找出问题所在。我们从SQL Server的Dimension.City表中获取了数据,对吗?单击“编辑查询”,然后再次查看“位置”列。

Power BI桌面电源查询

在执行任何转换之前,请从“查询设置”窗格中单击“导航”步骤,以查看我们原来在“位置”列中的内容。

Power BI桌面电源查询

让我们来看看Dimension.City,看看我们在源表中真正拥有的是什么。

  • 打开SQL Server Management Studio(SSMS)并连接到托管WideWorldImportersDW数据库的SQL Server实例
  • 查询维度城市

SQL服务器地理数据类型

SQL服务器中的“位置”列数据类型为“地理”,这意味着我们可以轻松地从SSMS的位置列中获取经度和纬度。现在,运行以下查询以从“位置”列获取纬度和经度:

选择 [城市密钥],

       [第一次世界大战城市编号],

       ,

       [位置],

       [位置].纬度 纬度,

       [位置]. 经度

   尺寸.

SQL服务器地理数据类型Location.Lat 位置长

注意: 地理是SQL Server中可用的.NET公共语言表达式(CLR)数据类型。在上面的查询中,“。Lat”和“ .Long”是地理实例的属性。地理位置的属性区分大小写,因此,如果您输入“ [位置] .lat”而不是“ [位置] .Lat”,则会收到以下错误消息:

找不到属性或字段‘lat’ for type ‘Microsoft.SqlServer.Types.SqlGeography’ in assembly ‘Microsoft.SqlServer.Types’.

要了解有关SQL Server中地理数据类型的更多信息,请参考以下链接:

好的,让我们同时看一下SQL查询和Power Query结果。

地理能力查询与SQL Server

好吧,我们现在发现了这个问题。由于某种原因,Power Query将SQL Server地理数据类型错误地转换为文本。转换模式应为“ POINT(经度)”,而不是“ POINT(经度)”。看起来像个错误。我已经将此问题报告给Power BI团队。如果您还想报告它,请单击 这里.

但是,在Power BI团队解决此问题之前我们应该怎么做?好吧,我们可以通过两种方式克服这一点。

  • 我们可以将Power Query中的“ 位置”列拆分为两列,即“纬度”和“经度”
  • 我们可以使用之前编写的SQL查询,然后再次导入“ 尺寸.City”表
解决方案:将“位置”列拆分为纬度和经度

当我们将“位置”列从“ POINT(-78.651695 42.1083969)”转换为“ -78.651695,42.1083969”时,我们已经完成了上一部分的一半。因此,我们实际上需要将“位置”列分为纬度和经度。

  • 点击“编辑查询”
  • 从“查询的”窗格中浏览“ 尺寸 市”
  • 右键单击“位置”,然后单击“拆分列”和“按分隔符”

Power BI桌面 Power查询拆分列

  • 选择“逗号”作为分隔符,然后单击“确定”

Power BI桌面 Power查询拆分列

  • 将“ 位置.1”重命名为“经度”,将“ 位置.2”重命名为“纬度”

Power BI桌面 Power查询纬度经度

  • Click 关闭并申请
  • 当我们将“位置”列拆分为两个新列时,地图中断,请单击“修复此”

Power BI桌面地图

  • 更改“纬度”和“经度”列的数据类别
  • 将“纬度”和“经度”列的“默认汇总”属性设置为“不汇总”
  • 点击地图
  • 在地图上拖放“纬度”和“经度”列

Power BI桌面地图纬度经度

该问题已解决。

支持CLR类型的电源查询问题‘Microsoft.SqlServer.Types.SqlGeography’

当您要从查询地理类型列的SQL查询导入数据时,会发生此问题。有时您需要通过运行SQL语句从SQL Server获取数据。例如,在我们的示例数据库中,查看“ 尺寸.City”表。 “有效期自”和“有效期至”列表示尺寸变化缓慢,这意味着Dimension.City中的所有行均无效。保持Power BI模型大小最佳的最佳实践是不导入所需的数据。这也提高了我们模型的性能。因此,我有兴趣从Dimension.City表中仅导入有效行,这些行的“有效期至”等于“ 9999/12/31”。

  • 从SQL Server数据库获取数据
  • 输入“服务器”和“数据库”名称
  • 单击“高级”,然后在下面键入SQL语句,然后单击“确定”。

选择 *

尺寸.

哪里 ([有效]) = 9999

Power BI桌面获取数据SQL语句

  • 查看“位置”列

DataSource.Error:我们不't support CLR type 'Microsoft.SqlServer.Types.SqlGeography'

  • 它带来了“错误”,但是为什么呢?点击“编辑”以查看“位置”有什么问题
  • 单击有错误的单元格

DataSource.Error:我们不't support CLR type 'Microsoft.SqlServer.Types.SqlGeography'

我们得到了“ DataSource.Error:我们没有’t support CLR type ‘Microsoft.SqlServer.Types.SqlGeography’”错误。嗯,看起来不太好。好吧,让我们继续解决此问题。

解决方案:使用T-SQL从“位置”获取“纬度”和“经度”属性

通过修改我们的T-SQL,我们可以轻松解决此问题。我曾解释说,本文前面我们可以从SQL Server中的地理位置数据类型获取“纬度”和“经度”。我们使用相同的技术来解决此问题。您需要做的就是调用“位置”的“纬度”和“经度”属性。因此,SQL语句将如下所示:

选择      [城市密钥],

                     [第一次世界大战城市编号],

                     ,

                     [州省],

                     国家,

                     大陆,

                     [销售地区],

                     地区,

                     次区域,

                     位置.纬度 纬度,

                     位置. 经度,

                     康卡特(位置.纬度

                            , ‘, ‘

                              , 位置.

                              ) 位置,

                     [最新记录的人口],

                     [有效期],

                     [有效],

                     [沿袭键]

   尺寸.

哪里  (([有效]) = 9999)

 

要用新的SQL语句替换旧的SQL语句,只需点击齿轮图标(Power BI桌面编辑源),位于“查询设置”窗格中“源”步骤的右侧。

Power BI桌面编辑源

单击确定,然后单击“关闭”。 &应用”以获取没有错误的正确结果。结果集中的唯一区别是,我们现在还有另外两列显示纬度和经度。我们将纬度和经度连接起来以支持“位置”。

在下一节中,我将解决Power View和Power 地图中的相同问题。

威力观和Power 地图的数据准备(Excel 2016和2013)

有多种方法可以为Excel 2016中的Power View和Power 地图准备数据。

  • 将数据从SQL Server直接导入Excel
  • 将数据从SQL Server直接导入Power Pivot模型
  • 连接到SQL Server,使用Power Query导入和转换数据,然后将结果加载到Excel工作表或Power Pivot数据模型

为了使这一部分尽可能简单,我直接解释了如何将数据加载到Power Pivot中。

  • 打开Excel 2016
  • 单击功能区上“数据”选项卡中的“管理数据模型”

电子表格 2016管理数据模型

  • 通过单击“从数据库”然后单击“从SQL Server”获取外部数据

电子表格 2016 动力枢轴获取数据

  • 输入服务器名称,然后选择“ WideWorldImportersDW”数据库,然后单击“下一步”

电子表格 2016 动力枢轴获取数据

  • 选择如何导入数据(保持默认设置),然后单击“下一步”。

电子表格 2016 动力枢轴获取数据

  • 从列表中选择“城市”和“销售”,然后单击“完成”

电子表格 2016 动力枢轴获取数据

电子表格 2016 动力枢轴获取数据

注意: 如果您使用Excel 2013,请不要担心,因为它与Excel 2016没什么不同。您只需要使用功能区上的Power Pivot选项卡导入数据

威力观和Power 地图中的地图问题

威力观和Power 地图中的错误城市

威力观和Power 地图中也会发生相同的情况。当您在地图上放置“城市”时,它会显示错误的城市。这很简单,因为所有这些工具都使用Bing 地图引擎来可视化地图数据。

这是在Power View中获得的内容:

电子表格 2016 威力观

下面的屏幕截图来自Power 地图:

电子表格 2016功率图

解决方案:在Power Pivot中合并国家和城市

我们通过编写一个非常简单的DAX表达式将Power BI中的Country和City串联在一起。该原理适用于Power Pivot。

这是在添加新的计算列(将“国家/地区”和“城市”列Power Pivot连接起来)后在Power View和Power 地图中得到的结果。

威力观结果:

电子表格 2016 威力观

功率图结果:

电子表格 2016功率图

动力枢轴忽略具有“地理”数据类型的列

就像您要在Power BI中导入SQL查询结果时在Power BI中遇到的情况一样,如果我们想在Power Pivot中执行相同的操作,我们会看到Power Pivot在编写以下查询时仅忽略了“ 位置”列。动力枢轴:

选择 *

尺寸.

哪里 ([有效]) = 9999

电子表格 2016 动力枢轴表导入向导

这是我们在Power Pivot中获得的:

电子表格 2016 动力枢轴

如您所见,没有“位置”列。

同样,解决方案是使用T-SQL从“位置”获取“纬度”和“经度”属性。因此,在Power Pivot的功能区中,从“设计”选项卡中单击“表属性”,然后修改SQL语句,如下所示:

电子表格 2016 动力枢轴表编辑表属性

结论

如果您正在处理涉及地理空间数据的项目,并且您的源数据库是SQL Server,那么请注意Power BI,Power Pivot和Power Query与SQL Server地理数据类型不兼容。如果您未指定所需的城市,则Power BI地图可视化,Power View地图或Power 地图可视化中的城市可能会出现错误。

在不知道如何解决这些问题的情况下首次面对上述问题可能会很烦且代价高昂。因此,我希望Microsoft在Power BI和Excel 2016的下一个发行版/更新中解决此类问题。

如果您遇到其他任何问题,请与我们分享。那么,为什么不在下面的评论部分中撰写您的故事呢?

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.