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屋!