Power Query:当特定值出现在另一列中时如何将一个添加到列中 [英] Power Query: how to add one to a column when a specific values appear in an other column
问题描述
我有一个 ID 列，并且我正在寻找每次特定项目出现在我的 Geography 列(ItalyZ、ItalyM、UKY 或 UKM).
ItalyZ的ID从0开始，到4000结束.
ItalyB的ID从4000开始，到8000结束.
UKY的ID从0开始，到4000结束.
UKM的ID从4000开始，到8000结束.
但是，我正在刷新我的文件，因此我将不时有新到的地理"没有起源或第一个 ID.这些边界/范围仅是已知的开始和结束.
这是我的数据示例:
|---------------------|-----------------||身份证 |地理 ||----------------------|------------------||AB0000 |意大利Z ||----------------------|------------------||AB4041 |意大利B ||----------------------|------------------||BC0000 |英国 ||----------------------|------------------||BC4001 |联合王国 ||----------------------|------------------||空 |意大利Z ||----------------------|------------------||空 |意大利Z ||----------------------|------------------||空 |英国 ||----------------------|------------------||空 |联合王国 ||----------------------|------------------|这是我的预期输出:
|---------------------|-----------------||身份证 |地理 ||----------------------|------------------||AB0000 |意大利Z ||----------------------|------------------||AB4041 |意大利B ||----------------------|------------------||BC0000 |英国 ||----------------------|------------------||BC4001 |联合王国 ||----------------------|------------------||AB0001 |意大利Z ||----------------------|------------------||AB0001 |意大利Z ||----------------------|------------------||AB4042 |英国 ||----------------------|------------------||BC0001 |联合王国 ||----------------------|------------------|我一直在尝试许多不同的方法并尝试调整运行的整体解决方案.我也一直在尝试将我的文件分成四个不同的文件，以免在不同情况下交替使用 If 函数，从而使其更简单，就像我的电源查询中这样:
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Sum", each if [Geography] = "UKM" then [Number AB range below 4000] + 1else if [Geography] = "UKY" then [Number AB range above 4000] + 1else if [Geography] = "ItalyB" then [Number BC range above 5000]else [数字 BC 范围低于 5000] + 1)但绝对没有任何效果.这令人抓狂.
在我的其他答案中，这是一个忽略您拥有的 ID 字母前缀的简化问题.
ID，组 |新身份证------------|------4、A |47、A |7300，乙300525，C |525空，一个 |109、A |9空，一个 |11空，B |301空，C |526空，一个 |12空，B |302从表格左侧开始，我们要计算新列NewID.
在这个答案中，我将编写一个使用
您可以通过选择现有表中的现有列并单击调用"按钮来使用它.
这将在您的查询窗格中创建一个名为 Invoked Function 的新列表，该列表是应用于您选择的列的函数.
您还可以创建一个空白查询并将其传递给一个列表.例如，FilterNulls({4,7,null,9,null,null}) 返回 {4,7,10,9,11,12}.p>
这是查询编辑器中的样子.
<小时>我们真正想做的是将这个函数作为分组操作中的列变换，然后展开:
让Source = <Data Table Source Here>,#"Grouped Rows" = Table.Group(Source, {"Group"}, {{"FillNulls", each FillNulls([ID]), type list}}),#"Expanded FillNulls" = Table.ExpandListColumn(#"Grouped Rows", "FillNulls")在#"扩展填充空"这是分组后展开前的样子:
注意函数在做什么.我们在 ID 列上为每个单独的 Group 应用函数 FillNulls.
这与其他答案的步骤和复杂性相似，但使用的函数以您可能更熟悉的递归方式构造.
I have an ID column and I am looking for ways to increment my IDs each time a specific item appears in my Geography column (ItalyZ, ItalyM, UKY or UKM) is found.
The ID of ItalyZ starts at 0 and ends at 4000.
The ID of ItalyB starts at 4000 and ends at 8000.
The ID of UKY starts at 0 and ends at 4000.
The ID of UKM starts at 4000 and ends at 8000.
However, I am refreshing my file, and I will thus have from time to time new arrivals of "geographies" without the origins or first IDs. These boundaries/ranges are only known beginning and ends.
Here is a sample of my data:
|---------------------|------------------|
| ID | Geography |
|---------------------|------------------|
| AB0000 | ItalyZ |
|---------------------|------------------|
| AB4041 | ItalyB |
|---------------------|------------------|
| BC0000 | UKY |
|---------------------|------------------|
| BC4001 | UKM |
|---------------------|------------------|
| NULL | ItalyZ |
|---------------------|------------------|
| NULL | ItalyZ |
|---------------------|------------------|
| NULL | UKY |
|---------------------|------------------|
| NULL | UKM |
|---------------------|------------------|
Here is my expected output :
|---------------------|------------------|
| ID | Geography |
|---------------------|------------------|
| AB0000 | ItalyZ |
|---------------------|------------------|
| AB4041 | ItalyB |
|---------------------|------------------|
| BC0000 | UKY |
|---------------------|------------------|
| BC4001 | UKM |
|---------------------|------------------|
| AB0001 | ItalyZ |
|---------------------|------------------|
| AB0001 | ItalyZ |
|---------------------|------------------|
| AB4042 | UKY |
|---------------------|------------------|
| BC0001 | UKM |
|---------------------|------------------|
I have been trying many various ways and trying to adapt running total solutions. I have also been trying to break apart my file in four different ones in order not to have an If function alternating between cases, and thus making it simpler, like this in my power query:
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Sum", each if [Geography] = "UKM" then [Number AB range below 4000] + 1
else if [Geography] = "UKY" then [Number AB range above 4000] + 1
else if [Geography] = "ItalyB" then [Number BC range above 5000]
else [Number BC range below 5000] + 1)
But absolutely nothing works. This maddening.
As in my other answer, here's a simplified problem ignoring the ID letter prefixes you have.
ID, Group | NewID
-----------|------
4, A | 4
7, A | 7
300, B | 300
525, C | 525
null, A | 10
9, A | 9
null, A | 11
null, B | 301
null, C | 526
null, A | 12
null, B | 302
Starting from the left part of the table, we want to compute the new column NewID.
In this answer, I will write a custom function that's written recursively using the List.Generate function.
From the linked documentation the function is set up like this
List.Generate(
initial as function, /*Set all your initial variables*/
condition as function, /*Stopping criteria.*/
next as function, /*Define how to update at each step.*/
optional selector as nullable function /*Pick output element.*/
) as list
Define a function that takes a column potentially containing nulls and fills the nulls incrementally from the maximal non-null value:
(Column as list) as list =>
let
Generate =
List.Generate(
() => [x = Column{0}, i = 0, n = List.Max(Column)],
each [i] < List.Count(Column),
each [
i = [i] + 1,
x = if Column{i} = null then [n] + 1 else Column{i},
n = if Column{i} = null then [n] + 1 else [n]
],
each [x]
)
in
Generate
When you define the function, it looks like this and can be re-used in any other queries:
You can use it by choosing an existing column in an existing table and clicking the Invoke button.
This will create a new list in your Queries pane named Invoked Function that is that function applied to the column you selected.
You can also create a blank query and pass it a list. For example, FilterNulls({4,7,null,9,null,null}) returns {4,7,10,9,11,12}.
Here's what that looks like in the query editor.
What we really want to do is use this function as a column transformation in a group by operation and then expand:
let
Source = <Data Table Source Here>,
#"Grouped Rows" = Table.Group(Source, {"Group"}, {{"FillNulls", each FillNulls([ID]), type list}}),
#"Expanded FillNulls" = Table.ExpandListColumn(#"Grouped Rows", "FillNulls")
in
#"Expanded FillNulls"
Here's what it looks like after grouping but before expanding:
Notice what the function is doing. We're applying the function FillNulls on the ID column for each separate Group.
This is a similar amount of steps and complexity as the other answer but uses a function that is constructed in a recursive way that may be more familiar to you.
这篇关于Power Query:当特定值出现在另一列中时如何将一个添加到列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
