if-statement - Power Query:当特定值出现在另一列中时如何将一个添加到列中
问题描述
我有一个 ID 列,并且我正在寻找每次在我的Geography
列(ItalyZ
、或)中ItalyM
出现特定项目时增加我的 ID 的方法。UKY
UKM
的 IDItalyZ
从 0 开始,到 4000 结束。
的IDItalyB
从4000开始,到8000结束。
的 IDUKY
从 0 开始,到 4000 结束。
的IDUKM
从4000开始,到8000结束。
但是,我正在刷新我的文件,因此我将不时有新来的“地理”没有起源或第一个 ID。这些边界/范围仅是已知的开始和结束。
这是我的数据示例:
|---------------------|------------------|
| ID | Geography |
|---------------------|------------------|
| AB0000 | ItalyZ |
|---------------------|------------------|
| AB4041 | ItalyB |
|---------------------|------------------|
| BC0000 | UKY |
|---------------------|------------------|
| BC4001 | UKM |
|---------------------|------------------|
| NULL | ItalyZ |
|---------------------|------------------|
| NULL | ItalyZ |
|---------------------|------------------|
| NULL | UKY |
|---------------------|------------------|
| NULL | UKM |
|---------------------|------------------|
这是我的预期输出:
|---------------------|------------------|
| ID | Geography |
|---------------------|------------------|
| AB0000 | ItalyZ |
|---------------------|------------------|
| AB4041 | ItalyB |
|---------------------|------------------|
| BC0000 | UKY |
|---------------------|------------------|
| BC4001 | UKM |
|---------------------|------------------|
| AB0001 | ItalyZ |
|---------------------|------------------|
| AB0001 | ItalyZ |
|---------------------|------------------|
| AB4042 | UKY |
|---------------------|------------------|
| BC0001 | UKM |
|---------------------|------------------|
我一直在尝试许多不同的方法并尝试调整运行的整体解决方案。我也一直在尝试将我的文件分成四个不同的文件,以免在不同情况下交替使用 If 函数,从而使其更简单,就像我的电源查询中这样:
#"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)
但绝对没有任何效果。这令人抓狂。
解决方案
我将回答一个进一步简化的问题,因为我不想解决ID
字母前缀。
假设我们有下表(我已经包括:
ID, Group
-----------
0, A
1, A
300, B
525, C
null, A
null, B
null, B
null, C
并希望生成一个NewID
将替换的新列ID
。
ID, Group, NewID
------------------
0, A, 0
1, A, 1
300, B, 300
525, C, 525
null, A, 2
null, B, 301
null, B, 302
null, C, 526
这是一个使用的方法Table.AddIndexColumn
:
let
Source = <First Table Above>,
#"Grouped Rows" = Table.Group(Source, {"Group"}, {{"ID", each List.Max([ID]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (C) => Table.AddIndexColumn(Table.SelectRows(Source, each _[Group] = C[Group]),"NewID",C[ID],1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"NewID"}, {"NewID"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ID"})
in
#"Removed Columns"
首先,我们分组Group
以找到最大值ID
per Group
:
然后我们添加一个新列,其中列中的每一行都是一个表,通过将原始表过滤到当前组,然后添加一个从ID
我们刚刚找到的最大值开始的索引列。这是最复杂的一步。
从这里,我们展开Custom
表格列(选择我们还没有的列)并删除旧ID
列。我们现在需要缺少我们选择执行的任何排序或列类型。
编辑:我在上面犯了一个错误。请注意,NewID
for Group
A1,2,3
不是0,1,2
我尝试的。
要为这个简单的示例解决此问题,您可以在 group-by 步骤中使用List.Min
而不是。List.Max
对于更复杂的示例,您可能需要向源表添加一个索引列,以便您可以在扩展后合并回它,并且只将新NewID
的用于以前的空ID
值,因为我们不能保证它们是连续的。
这是代码:
let
Source = <First Table Above>,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Group"}, {{"ID", each List.Max([ID]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (C) => Table.AddIndexColumn(Table.SelectRows(Table.Sort(#"Added Index",{"ID"}), each _[Group] = C[Group]),"NewID",C[ID]+1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index", "NewID"}, {"Index", "NewID"}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Expanded Custom", {"Index"}, "Expanded Custom", JoinKind.LeftOuter),
#"Expanded Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom", {"NewID"}, {"NewID"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Expanded Custom", "ReplaceID", each if [ID] = null then [NewID] else [ID]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ID", "NewID"})
in
#"Removed Columns"
复杂的步骤只是稍微改变了一点:
(C) => Table.AddIndexColumn(
Table.SelectRows(
Table.Sort(#"Added Index", {"ID"}),
each _[Group] = C[Group]
),
"NewID", C[ID] + 1, 1
)
不同之处在于我们需要添加一个排序,以便空值出现在所有已分配的ID
值之后,并开始索引空值C[ID] + 1
而不是C[ID]
。
这是一个步骤更少(没有分组、扩展或合并)但功能更复杂的版本:
let
Source = <First Table Above>,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (C) => Table.SelectRows(#"Added Index", each _[Group] = C[Group])),
#"Added NewID" = Table.AddColumn(#"Added Custom", "NewID", (C) => if C[ID] = null then Table.SelectRows(Table.AddIndexColumn(Table.SelectRows(C[Custom], each _[ID] = null), "NewID", List.Max(C[Custom][ID])+1,1), each _[Index] = C[Index]){0}[NewID] else C[ID]),
#"Removed Columns" = Table.RemoveColumns(#"Added NewID",{"Custom"})
in
#"Removed Columns"
第一个添加的Custom
列只是过滤到当前的索引源表Group
。然后我们添加NewID
定义为的列:
(从内到外阅读。)
(C) =>
if C[ID] = null
then Table.SelectRows(
Table.AddIndexColumn(
Table.SelectRows(C[Custom], each _[ID] = null),
"NewID", List.Max(C[Custom][ID]) + 1, 1
),
each _[Index] = C[Index]
){0}[NewID]
else C[ID]
与之前类似,我们采用 group subtable Custom
,只需选择空ID
行并从最大非空ID
加一开始对它们进行索引。这仍然给我们留下了一个表,所以我们只想要这个子表中对应于Index
整个表的行。我们使用{0}[NewID]
从列中表的第一(唯一)行的单元格中提取值[NewID]
。对于非空ID
值,else 子句只是让它们保持原样。
推荐阅读
- javascript - (节点:9263)UnhandledPromiseRejectionWarning:ValidationError:想法验证失败:imageUrl:需要路径`imageUrl`
- reactjs - 从 prop 数组映射的复选框的“已检查”状态与 array.find 结果与第二个数组不匹配
- java - bean 类 [java.util.ArrayList] 的无效属性“xyz”:bean 属性“xyz”不可读或具有无效的 getter 方法
- javascript - 将方法的参数声明为接口类型,并将不相关的对象传递给它
- sql - 如何使用 regexp_substr 提取包名
- javascript - 例如,如果有人试图复制粘贴文本,我如何只允许数字输入做出反应
- python - 使用 Django Channels 向多个组发送消息
- java - 我无法解决错误:找不到资源 drawable/abc (aka com.example.myapplication:drawable/abc)
- django-rest-framework - 如何从 GET 请求中获取 JSON 数据(正文应用程序/json)?
- performance - 先进先出库存(FIFO)公式计算很慢