从Power BI / Power Query中的表表中添加先前值的列 [英] Add column of previous values from table of tables in Power BI / Power Query

问题描述

正在寻找 Max Zelensky的解决方案的后续操作将每个记录的发生次数分配给每个记录分类计数。假设原始示例具有一个[Date]字段，我试图再上一层，并添加一列以显示先前的[Date]值

Looking for a followup to Max Zelensky's solution here. Assuming the original example had a [Date] field, I'm trying to go one more level and add a column that shows the prior [Date] value

还此处和此处和此处。

1. 我已经创建了表格表的数量：

1. Per Max, I've created the table of tables:

• AddedCustom = Table.AddColumn（GroupedRows， Custom，每个Table.AddIndexColumn（[tmp ]， Occurrence，1,1）类型表）

• AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([tmp],"Occurrence", 1,1) type table)

创建第二个索引：

• SecondIndex = Table.AddColumn（AddedCustom， Custom .2，每个Table.AddIndexColumn（[Custom]， Occurance.2，0,1），键入table）

• SecondIndex= Table.AddColumn(AddedCustom, "Custom.2", each Table.AddIndexColumn([Custom],"Occurance.2", 0,1), type table)

我已经成功添加了引用当前[日期]行的列：

I've successfully added a column that references the current [Date] row:

• CurrentDate = Table.AddColumn（SecondIndex， Date.2，每个Table.AddColumn（[Custom.2]， Date.2，每个[Date]），键入表）

• CurrentDate= Table.AddColumn(SecondIndex, "Date.2", each Table.AddColumn([Custom.2],"Date.2", each [Date]), type table)

但是当我尝试引用任一索引列（甚至只是放入{0}）时，新字段错误出来。我可以肯定地说我在表的列中引用表中的行的语法中缺少一些东西，但是我不确定如何到达那里-我尝试过的一些例子没有成功：

But when I try to reference either index column (even just putting in {0}), the new field errors out. I'm fairly certain I'm missing something in the syntax of referencing rows within a table within a column of tables, but I'm just not sure how to get there -- A few examples I've tried without success:

• PriorDate = Table.AddColumn（SecondIndex， PriorDate，每个Table.AddColumn（[Custom.2 ]， Prior Date，每个{0} [Date]），键入table）
  -只是看看我是否可以从第一行返回该值

• PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each {0}[Date]), type table) -- just to see if I could return the value from the first row

PriorDate = Table.AddColumn（SecondIndex， PriorDate，每个Table.AddColumn（[Custom.2]， Prior Date，每个{[ Occurance.2]} [Date]），键入table）-不适用于[Occurance]或[Occurance.2]

PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each {[Occurance.2]}[Date]), type table) --doesn't work for [Occurance] or [Occurance.2]

PriorDate = Table.AddColumn（SecondIndex， PriorDate，每个Table.AddColumn（[Custom.2]， Prior Date，每个{[Occurance] -1} [Date]） ，键入表）

PriorDate = Table.AddColumn（SecondIndex， PriorDate，每个表。 AddColumn（[Custom.2]， Prior Date，每个[Custom.2] {0} [Date]），键入t

PriorDate = Table.AddColumn（SecondIndex， PriorDate，每个Table.AddColumn（ [Custom.2]，优先日期，每个Table.SelectColums（[Custom.2]，[Date]）），键入table）

此外，有人可以指出我对优化#Tables，{Lists}的语法和机制的良好参考吗？ ，[唱片]等。我会很感激（我已经读过Ken Puls的书的第20章，但还没有完全停下来）。

Also, can anyone point me to a good reference of the syntax and mechanics for optimizing #Tables, {Lists}, [Records] etc. I would appreciate it (I've read chapter 20 of Ken Puls' book a few times, but it hasn't quite stuck yet). Thanks in advance!

| Name | Date     | Occurance | Prior Date (Desired) |
|------|----------|-----------|----------------------|
| A    | 1/1/2019 | 1         | null/error           |
| A    | 3/1/2019 | 2         | 1/1/2019             |
| B    | 2/1/2019 | 1         | null/error           |
| A    | 4/1/2019 | 3         | 3/1/2019             |
| B    | 5/1/2019 | 2         | 2/1/2019             |

推荐答案

类似于我的在此处回答，您可以添加两个索引，而不是仅添加一个索引，一个从<$开始c $ c> 0 和一个从 1 开始的值，我们通过执行自合并来计算前一行。

Similar to my answer here, instead of adding just one index, you can add two, one starting from 0 and one starting from 1, which we use to calculate the previous row by performing a self merge.

let
    Source = Table.FromRows({{"A",#date(2019,1,1)},{"A",#date(2019,1,3)},{"B",#date(2019,1,2)},{"A",#date(2019,1,4)},{"B",#date(2019,1,5)}}, {"Name", "Date"}),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    GroupByName = Table.Group(ChangeTypes, {"Name"}, {{"tmp", each _, type table}}),
    AddIndices = Table.AddColumn(GroupByName, "Custom", each Table.AddIndexColumn(Table.AddIndexColumn([tmp],"Occurrence", 1,1),"Prev",0,1)),
    ExpandTables = Table.ExpandTableColumn(AddIndices, "Custom", {"Date", "Occurrence", "Prev"}, {"Date", "Occurrence", "Prev"}),
    SelfMerge = Table.NestedJoin(ExpandTables,{"Name", "Prev"},ExpandTables,{"Name", "Occurrence"},"Expanded Custom",JoinKind.LeftOuter),
    ExpandPriorDate = Table.ExpandTableColumn(SelfMerge, "Expanded Custom", {"Date"}, {"Prior Date"}),
    RemoveExtraColumns = Table.RemoveColumns(ExpandPriorDate,{"Prev", "tmp"})
in
    RemoveExtraColumns

这篇关于从Power BI / Power Query中的表表中添加先前值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！