多条件的表,如何逆求和计算单个值?

Power Query 天天吃鸡 ⋅ 于 2019-07-10 22:29:33 ⋅ 最后回复由 kirete 2019-07-22 17:02:00 ⋅ 3084 阅读

月份 条件1 条件2 累计值 当月值
2017/1/1 A 甲 10 10
2017/2/1 A 甲 7 -3
2017/3/1 A 甲 43 36
2017/1/1 B 甲 5 5
2017/2/1 B 甲 23 18
2017/3/1 B 甲 44 21
2017/1/1 A 乙 9 9
2017/2/1 A 乙 22 13
2017/3/1 A 乙 41 19
2017/1/1 B 乙 8 8
2017/2/1 B 乙 23 15
2017/3/1 B 乙 47 24
file
累计值求当月值

源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    类型 = Table.Buffer(Table.TransformColumnTypes(源,{{"月份", type date}, {"条件1", type text}, {"条件2", type text}, {"累计值", Int64.Type}})),
    排序 = Table.Sort(类型,{{"条件1", Order.Ascending}, {"条件2", Order.Ascending}, {"月份", Order.Ascending}}),
    索引0 = Table.AddIndexColumn(排序, "索引_0", 0, 1),
    索引1 = Table.AddIndexColumn(排序, "索引_1", 1, 1),
    分组初始值 = Table.Group(排序, {"条件1", "条件2"}, {{"初始值", each List.FirstN([累计值],1){0},Int64.Type}}),
    合并初始值 = Table.NestedJoin(索引0, {"条件1", "条件2"}, 分组初始值, {"条件1", "条件2"}, "分组初始值", JoinKind.LeftOuter),
    展开初始值 = Table.ExpandTableColumn(合并初始值,"分组初始值",{"初始值"}),
    合并索引1 = Table.NestedJoin(展开初始值, {"索引_0"}, 索引1, {"索引_1"}, "索引_1", JoinKind.LeftOuter),
    展开索引1 = Table.ExpandTableColumn(合并索引1, "索引_1", {"累计值"}, {"累计值1"}),
    当前值 = Table.AddColumn(展开索引1, "计算当前值", each if [累计值]=[初始值] then [累计值] else [累计值]-[累计值1],Int64.Type),
    结果 = Table.SelectColumns(当前值,{"月份", "条件1", "条件2", "累计值", "计算当前值"})
in
    结果
let
    源 = Excel.CurrentWorkbook(){[Name="表4"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"年月", type date},{"本年同期累计", type number}}),
    分组的行 = Table.Group(更改的类型, {"年月", "公司", "险类3", "项目"}, {{"本年同期累计", each List.Sum([本年同期累计]), type number}}),
    排序 = Table.Sort(分组的行,{{"公司", Order.Ascending}, {"险类3", Order.Ascending}, {"项目", Order.Ascending}, {"年月", Order.Ascending}}),
    索引0 = Table.AddIndexColumn(排序, "索引_0", 0, 1),
    索引1 = Table.AddIndexColumn(排序, "索引_1", 1, 1),
    分组初始值 = Table.Group(排序, {"公司", "险类3","项目"}, {{"初始值", each List.FirstN([本年同期累计],1){0},Int64.Type}}),
    合并的查询 = Table.NestedJoin(索引0,{"公司", "险类3", "项目"},分组初始值,{"公司", "险类3", "项目"},"分组初始值",JoinKind.LeftOuter),
    展开初始值 = Table.ExpandTableColumn(合并的查询, "分组初始值", {"初始值"}, {"初始值"}),
    合并索引1 = Table.NestedJoin(展开初始值,{"索引_0"},索引1,{"索引_1"},"索引_1",JoinKind.LeftOuter),
    展开索引1 = try Table.ExpandTableColumn(合并索引1, "索引_1", {"本年同期累计"}, {"本年同期累计.1"}) otherwise null,
    排序的行 = Table.Sort(展开索引1,{{"公司", Order.Ascending}, {"险类3", Order.Ascending}, {"项目", Order.Ascending}, {"年月", Order.Ascending}}),
    已添加自定义 =  Table.AddColumn(排序的行, "计算当前值", each if [本年同期累计]=[初始值] then [本年同期累计] else [本年同期累计]-[本年同期累计.1],type number) 
in
    已添加自定义

对于只有第一个月有数据,后面2个月没数据的,Table.AddColumn(排序的行, "计算当前值", each if [本年同期累计]=[初始值] then [本年同期累计] else [本年同期累计]-[本年同期累计.1],type number) 这一步要怎么改?
file

最佳答案
  • 焦棚子 微软 MVP
    2019-07-13 23:22:00

    @天天吃鸡

    用一个逻辑来做吧,虽然长一点,效率高,亲测1万条,妙出。

    let
        源 = Excel.CurrentWorkbook(){[Name="demo"]}[Content],
        类型 = Table.Buffer(Table.TransformColumnTypes(源,{{"月份", type date}, {"条件1", type text}, {"条件2", type text}, {"累计值", Int64.Type}})),
        排序 = Table.Sort(类型,{{"条件1", Order.Ascending}, {"条件2", Order.Ascending}, {"累计值", Order.Ascending}}),
        索引0 = Table.AddIndexColumn(排序, "索引_0", 0, 1),
        索引1 = Table.AddIndexColumn(排序, "索引_1", 1, 1),
        分组初始值 = Table.Group(排序, {"条件1", "条件2"}, {{"初始值", each List.Min([累计值]), Int64.Type}}),
        合并初始值 = Table.NestedJoin(索引0, {"条件1", "条件2"}, 分组初始值, {"条件1", "条件2"}, "分组初始值", JoinKind.LeftOuter),
        展开初始值 = Table.ExpandTableColumn(合并初始值,"分组初始值",{"初始值"}),
        合并索引1 = Table.NestedJoin(展开初始值, {"索引_0"}, 索引1, {"索引_1"}, "索引_1", JoinKind.LeftOuter),
        展开索引1 = Table.ExpandTableColumn(合并索引1, "索引_1", {"累计值"}, {"累计值1"}),
        当前值 = Table.AddColumn(展开索引1, "计算当前值", each if [累计值]=[初始值] then [累计值] else [累计值]-[累计值1],Int64.Type),
        结果 = Table.SelectColumns(当前值,{"月份", "条件1", "条件2", "累计值", "计算当前值"})
    in
        结果
回复数量: 9
  • klzb
    2019-07-11 09:10:35

    就是和累加的操作一样,逆排序在操作,来个累减

  • 天天吃鸡
    2019-07-11 12:06:31

    @klzb 涉及7列(每列平均10项)左右的条件,如何累减呢

  • 焦棚子 微软 MVP
    2019-07-11 17:48:11
    let
        源 = Excel.CurrentWorkbook(){[Name="demo"]}[Content],
        类型 = Table.TransformColumnTypes(源,{{"月份", type date}, {"条件1", type text}, {"条件2", type text}, {"累计值", Int64.Type}, {"当月值", Int64.Type}}),
        计算 = Table.AddColumn(类型, "计算当月值", each  let t1= Table.SelectRows(Table.Group(类型, {"条件1", "条件2"}, {{"T", each _}}),(X)=>X[条件1]=[条件1] and X[条件2]=[条件2] )[T]{0} in  let n =List.PositionOf(t1[月份],[月份]) in  if n=0 then t1[累计值]{0} else t1[累计值]{n}- t1[累计值]{n-1} ,Int64.Type )
    in
        计算

    file

    你的2017/3/1 B 乙的当月值是错的吧?

  • 天天吃鸡
    2019-07-13 15:56:50

    @焦棚子 是错的:smile: ,老师数据量大的时候好卡,1秒钟2行数据,1个月4000多条数据,合计有7万多条数据

  • 焦棚子 微软 MVP
    2019-07-13 23:22:00

    @天天吃鸡

    用一个逻辑来做吧,虽然长一点,效率高,亲测1万条,妙出。

    let
        源 = Excel.CurrentWorkbook(){[Name="demo"]}[Content],
        类型 = Table.Buffer(Table.TransformColumnTypes(源,{{"月份", type date}, {"条件1", type text}, {"条件2", type text}, {"累计值", Int64.Type}})),
        排序 = Table.Sort(类型,{{"条件1", Order.Ascending}, {"条件2", Order.Ascending}, {"累计值", Order.Ascending}}),
        索引0 = Table.AddIndexColumn(排序, "索引_0", 0, 1),
        索引1 = Table.AddIndexColumn(排序, "索引_1", 1, 1),
        分组初始值 = Table.Group(排序, {"条件1", "条件2"}, {{"初始值", each List.Min([累计值]), Int64.Type}}),
        合并初始值 = Table.NestedJoin(索引0, {"条件1", "条件2"}, 分组初始值, {"条件1", "条件2"}, "分组初始值", JoinKind.LeftOuter),
        展开初始值 = Table.ExpandTableColumn(合并初始值,"分组初始值",{"初始值"}),
        合并索引1 = Table.NestedJoin(展开初始值, {"索引_0"}, 索引1, {"索引_1"}, "索引_1", JoinKind.LeftOuter),
        展开索引1 = Table.ExpandTableColumn(合并索引1, "索引_1", {"累计值"}, {"累计值1"}),
        当前值 = Table.AddColumn(展开索引1, "计算当前值", each if [累计值]=[初始值] then [累计值] else [累计值]-[累计值1],Int64.Type),
        结果 = Table.SelectColumns(当前值,{"月份", "条件1", "条件2", "累计值", "计算当前值"})
    in
        结果
  • 天天吃鸡
    2019-07-15 09:02:18

    @焦棚子
    老师好,对于只有第一个月有数据,后面2个月(或者一个月)没数据的,
    Table.AddColumn(排序的行, "计算当前值", each if [本年同期累计]=[初始值] then [本年同期累计] else [本年同期累计]-[本年同期累计.1],type number)

    这一步要怎么改?

  • 焦棚子 微软 MVP
    2019-07-15 14:43:26

    @天天吃鸡
    排序用日期就可以。
    jiaopengzi@qq.com

  • 天天吃鸡
    2019-07-15 22:04:39

    @焦棚子
    老师好,用了时间排序还是一样。比如1月数据为13,累计为13。 2月、3月当月值均为0,累计值1-3月均是13,就刚好匹配上了
    if [本年同期累计]=[初始值] then [本年同期累计] ,也就是当月值1-,3月都是13,而不是0,都是相同的,就没有计算 else 后面的 差值计算 13-13=0

  • kirete
    2019-07-22 17:02:00

    let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组合并 = Table.Combine(
    Table.Group(源,{"条件1","条件2"},{"Q",each
    [a=[累计值],
    b={a{0}}&List.Transform({1..List.Count(a)-1},(x)=>a{x}-a{x-1}),
    c=Table.FromColumns(Table.ToColumns()&{b},Table.ColumnNames()&{"当月值"})
    ][c]})[Q])
    in
    分组合并

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter