工作需要,想用 power query 作一个 现金日记账,但始终实现不了,请大神帮忙

Power Query txb ⋅ 于 2020-01-06 10:14:00 ⋅ 最后回复由 txb 2020-01-08 11:22:57 ⋅ 1015 阅读

工作需要,想用power query 作一个 现金日记账,但始终实现不了,主要是自动生成 余额 这列,以及自动生成本月小计的余额时不能实现,试了下 list.accumulate 函数,结果弄不出来,请大神帮忙。
file

本帖已被设为精华帖!
本帖由 Alex 于 6个月前 加精
成为第一个点赞的人吧 :bowtie:
回复数量: 6
  • geyee
    2020-01-06 16:14:06

    拼凑了一个,步骤应该可以优化下。
    file

    let
        源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA31DdU0lF6NmXb09alFRUVQLahkbGJnoU5kKUUqwNXZYSmysjYEE2FMVjF+qftuyAqgMjYxBRZhQmmCiMDA2QVpuhuMQACNHvM0F2CqcQc0yITUxSnWGBxiqEhinfA4TJnzbM5HU839L9YtxCiSik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [日期 = _t, 摘要 = _t, 借 = _t, 贷 = _t]),
        更改的类型 = Table.TransformColumnTypes(源,{{"日期", type date}, {"摘要", type text}, {"借", type number}, {"贷", Int64.Type}}),
        清除null值 = Table.ReplaceValue(更改的类型,null,0,Replacer.ReplaceValue,{"借","贷"}),
        求值表 = Table.RemoveLastN(清除null值),
        借贷表 = Table.SelectColumns(求值表,{"借","贷"}),
        余额 = List.Skip(
                    List.Accumulate(
                        Table.ToRows(借贷表),{0}, (state, current) =>state&{List.Sum({List.Last(state)}&{List.First(current)-List.Last(current)})})),
        小计 = Table.FromColumns(
                    Table.ToColumns(更改的类型)&{余额},Table.ColumnNames(源)&{"余额"}),
        结果 = Table.ReplaceMatchingRows(小计,{Table.Last(小计),Record.Combine({Table.Last(小计),[借=List.Sum(List.RemoveLastN(小计[借])),贷=List.Sum(List.RemoveLastN(小计[贷])),余额=List.Last(余额)]})})
    in
        结果
  • txb
    2020-01-06 21:02:12

    被余额和 结果这两步卡住了,能否细讲下这两步。

  • 飞天篮球 Talk is cheap, show me your code.
    2020-01-06 22:04:13

    试试List.Generate,加载20万行还是很快的

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        gen = let lst = List.Buffer(Table.ToRows(源)), 
                    n = List.Count(lst)
    
               in List.Generate( ()=>{{},"",0,0,0,0}, //{结果list,月份比较值,累计值,借方合计,贷方合计,索引}
                                 each _{5}<=n,
                                 each let l = lst{_{5}}?,
                                          c = if l=null then {} else l,
                                          m = Date.Month(c{0}?), 
                                          s = _{2}+List.Sum({c{2}?,c{3}?*-1}) 
    
                                       in if m=_{1} then { {c&{s}},m,s,List.Sum({_{3},c{2}?}),List.Sum({_{4},c{3}?}),_{5}+1}
                                          else {{{"","本月合计",_{3},_{4},_{2}},c&{s}},m,s,c{2}?,c{3}?,_{5}+1},
                                 each _{0} ),
    
        rlt = #table(Table.ColumnNames(源)&{"余额"}, List.Skip(List.Combine(gen)))
    in
        rlt
  • 飞天篮球 Talk is cheap, show me your code.
    2020-01-06 22:10:35

    左表变成右表

    file

  • wdx223
    2020-01-07 11:38:14

    按月分组,聚合求本月合计,然后插入到原表中

  • txb
    2020-01-08 11:22:57

    3楼,这个c和s 分别代表什么?

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