if-statement - 电源查询: how to add one to a column when a specific values appear in an other column

Question

我有一个 ID 列，并且我正在寻找每次特定项目出现在我的 Geography 列中时增加 ID 的方法(ItalyZ、ItalyM、UKY 或 UKM)已找到。

ItalyZ的ID从0开始，到4000结束。

ItalyB的ID从4000开始到8000结束。

UKY的ID从0开始，到4000结束。

UKM的ID从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)

但绝对没有任何效果。这令人抓狂。

Answer 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每Group :

然后我们添加一个新列，其中该列中的每一行都是一个表，通过将原始表过滤到当前组然后添加从最大值 ID 开始的索引列来定义。我们刚刚发现。这是最复杂的步骤。

从这里，我们扩展 Custom表列(选择我们还没有的列)并删除旧的 ID柱子。现在我们需要减少我们选择执行的任何排序或列类型。

---

编辑:我在上面犯了一个错误。请注意 NewID对于 Group A是1,2,3而不是0,1,2我一直在努力。

要解决这个简单示例的问题，您可以使用 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]

与之前类似，我们取组子表Custom ，只需选择 null ID行并从最大非空 ID 开始索引它们加一。这仍然给我们留下了一个表，所以我们只想要这个子表中与 Index 相对应的行。从整个表。我们使用{0}[NewID]从 [NewID] 中表格第一(唯一)行的单元格中提取值柱子。对于非空 ID值，else 子句只是让它们保持原样。