从 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. 每个 Max，我已经创建了表格:

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

• AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([tmp],"Occurrence", 1,1) 类型表)

创建了第二个索引:

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

我已成功添加引用当前 [Date] 行的列:

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

• 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", each Table.AddColumn([Custom.2],"Prior Date", each {0}[Date]), type 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", each Table.AddColumn([Custom.2],"Prior Date", each {[Occurance.2]}[Date]),类型表) -- 不适用于 [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", each Table.AddColumn([Custom.2],"Prior Date", each {[Occurance]-1}[Date]),类型表)

PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each [Custom.2]{0}[Date]), 输入表格)

PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each Table.SelectColums([Custom.2],[Date])), 输入表格)

另外，任何人都可以向我指出优化#Tables、{Lists}、[Records] 等的语法和机制的良好参考.我将不胜感激(我已经阅读了 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             |

推荐答案

类似于我的 在此回答，您可以添加两个，而不是只添加一个索引，一个从 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屋！