Power Query：当特定值出现在另一列中时如何向列添加一个

我有一个 ID 列，并且我正在寻找每次特定项目出现在我的列表中时增加我的 ID 的方法Geography柱子 （ItalyZ, ItalyM, UKY or UKM）被发现。

身份证号为ItalyZ从 0 开始，到 4000 结束。

身份证号为ItalyB从 4000 开始，到 8000 结束。

身份证号为UKY从 0 开始，到 4000 结束。

身份证号为UKM从 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:

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

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

Edit:我在上面犯了一个错误。请注意，NewID for Group A is 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，只需选择 nullID行并从最大非空值开始索引它们ID加一。这仍然给我们留下了一个表，所以我们只想要这个子表中与Index从整个表。我们用{0}[NewID]从表格中第一（唯一）行的单元格中提取值[NewID]柱子。对于非空ID值，else 子句只是让它们保持原样。