PowerQuery - 根据特定模式填充缺失数据 [英] PowerQuery - Fill missing data according to specific pattern

问题描述

我正在尝试清理从 Excel 文件接收的数据，并使用 PowerQuery(在 PowerBI 中)将其转换为可用格式.在示例表下方，以及我正在尝试执行的操作:

I am trying to clean data received from an Excel file and transform it using PowerQuery (in PowerBI) into a useable format. Below a sample table, and what I am trying to do:

| Country   | Type of location  |
|---------  |------------------ |
| A         | 1                 |
|           | 2                 |
|           | 3                 |
| B         | 1                 |
|           | 2                 |
|           | 3                 |
| C         | 1                 |
|           | 2                 |
|           | 3                 |

如您所见，我有每个国家/地区的位置类型列表(始终保持不变，每个国家/地区的数字始终相同，即每个国家/地区有 3 行用于 3 种位置类型)

As you can see, I have a list of location types for each country (always constant, always the same number per country, ie each country has 3 rows for 3 location types)

我想要做的是看看是否有办法填充国家"中的空单元格.列，带有适当的国家/地区名称，它会给出如下内容:

What I am trying to do is to see if there is a way to fill the empty cells in the "Country" column, with the appropriate Country name, which would give something like this:

| Country   | Type of location  |
|---------  |------------------ |
| A         | 1                 |
| A         | 2                 |
| A         | 3                 |
| B         | 1                 |
| B         | 2                 |
| B         | 3                 |
| C         | 1                 |
| C         | 2                 |
| C         | 3                 |

目前我考虑使用一系列 if/else if 条件，但由于有 100 多个国家/地区，这似乎不是正确的解决方案.

For now I thought about using a series of if/else if conditions, but as there are 100+ countries this doesn't seem like the right solution.

有什么方法可以更有效地做到这一点?

推荐答案

正如 Murray 所提到的，Table.FillDown 函数效果很好，并且内置在查询编辑器的转换"选项卡下的 GUI 中:

As Murray mentions, the Table.FillDown function works great and is built into the GUI under the Transform tab in the query editor:

请注意，它只会向下填充以替换空值，因此如果这些行中有空字符串而不是空值，则需要先进行替换.该按钮位于 GUI 中的填充"按钮上方，您可以像这样使用对话框

Note that it only fills down to replace nulls, so if you have empty strings instead of nulls in those rows, you'll need to do a replacement first. The button for that is just above the Fill button in the GUI and you'd use the dialog box like this

或者只是使用它生成的 M 代码而不是 GUI:

or else just use the M code that this generates instead of the GUI:

= Table.ReplaceValue(#"Previous Step","",null,Replacer.ReplaceValue,{"Country"})

这篇关于PowerQuery - 根据特定模式填充缺失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！