首页 > 解决方案 > Power Query:当特定值出现在另一列中时如何将一个添加到列中

问题描述

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

的 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)

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

标签: if-statementpowerbiincrementpowerquerym

解决方案


我将回答一个进一步简化的问题,因为我不想解决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以找到最大值IDper Group

通过...分组

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

添加表格列

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

最终的


编辑:我在上面犯了一个错误。请注意,NewIDfor GroupA1,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 子句只是让它们保持原样。


推荐阅读