合并查询中 “其他” 情况应该怎样处理?

Power Pivot 阿森纳里 ⋅ 于 2020-01-20 22:02:47 ⋅ 最后回复由 阿森纳里 2020-02-10 21:45:26 ⋅ 1215 阅读

现在有一个姓名表:
file
假设现在搞了一场年会,根据抽奖情况给每名员工发奖金,抽奖结果在奖金表中:
file

现在目的是根据奖金表得出这张获奖情况表:
file

我用合并查询Table.NestedJoin却匹配不到除获奖的三名员工之外的其他员工,我只好先合并一次,展开生成一个新的“姓名1”列:
file
然后把匹配不到的null替换成“其他”,再根据“姓名1”列再次进行合并查询,最后得出需要的表:
file

但是这样感觉好麻烦,数据量大的时候合并查询两次影响效率,有没有好一点的办法呢?

案例下载:
链接:https://pan.baidu.com/s/1lJNiVNfjvzzxW_N1xNBprQ
提取码:8axb

点赞
成为第一个点赞的人吧 :bowtie:
回复数量: 9
  • 飞天篮球 Talk is cheap, show me your code.
    2020-01-20 22:42:08

    fyi... 以下List.ReplaceRange中常量3的意思是在奖金这个列表中,“其它”之前插入一个值:

    = Table.FromRecords( 
                        List.Transform( Table.ToRecords(姓名),
                                        (x)=> List.Mode(
                                                        List.ReplaceRange(Table.ToRecords(奖金),3,0,{x}),
                                                         each [姓名] ) &x ) 
                        )

    file

  • 阿森纳里 数据从业者,PBI爱好者
    2020-01-21 00:09:58

    @飞天篮球 老哥这个脑洞开得有点大啊,你是怎么想到这样做的?我看了一个多小时才看明白是怎么回事,思路够牛,不过这经过“table转record-插入list-找众数-替换field-record转table”这么一顿操作猛如虎,会不会影响效率啊,当数据量大的时候会不会刷新时间过长呢,我还是找个例子试试吧

  • deadzlq 无我,亦无期
    2020-01-21 08:35:10

    file

  • libo5563
    2020-01-21 11:51:12

    Table.AddColumn(源, "自定义", each [a=Table.SelectRows(奖金,(x)=>x[姓名]=[姓名]),b=if Table.RowCount(a)=0 then Table.LastN(奖金,1) else a][b])

    file

  • 阿森纳里 数据从业者,PBI爱好者
    2020-01-21 14:37:52

    @deadzlq 这个思路也很好,先在奖金[姓名]中做筛选,返回可做查询依据的MatchCol列,再用这列做查询,不过还有几点疑问:

    1. List.FirstN(奖金[姓名],3)是不是没有必要?是不是为了避免有个人就叫“其他”的这种情况?
    2. else "其他"不够灵活,如果奖金表中属性值变化了就不行了,比如写为了“其他人”,这条语句就得同时修改,是不是可以优化为else List.Last(奖金[姓名]),但是这样却也限定了其他情况必须放在奖金表的最后一行,否则就不行了,思来想去怎么都没有完美的解决办法
    3. 仔细想了想这样其实和我一开始的查询一次展开再替换null也差不多,都是把匹配不上的值做特殊处理,之后都得有二次查询之后再删除中间步骤列的一步,如果用if语句做处理的话,其实可以用好个函数进行判断,包括:
      if List.Contains(奖金[姓名],[姓名])
      if List.PositionOf(奖金[姓名],[姓名],Occurrence.First)<>-1
      if Table.PositionOf(奖金,[姓名=[姓名]],Occurrence.First,"姓名")<>-1

    还可以用List.Mode,这样可以省略if判断:
    (x)=> List.Mode(List.Combine({{x[姓名]},奖金[姓名]}))
    我又试了试List.FirstN、List.LastN、List.RemoveFirstN、List.RemoveLastN、List.Skip等函数,都不行,因为它们都没法设置匹配不到时返回特定值

    不知这些方法的效率怎样,哪种方法最好

  • 阿森纳里 数据从业者,PBI爱好者
    2020-01-21 14:41:44

    @libo5563 大神这个思路很好啊,我想这是模拟Table.NestedJoin的原理吧,不过可以设置匹配不到时返回特定的行,这样就可以省略中间步骤列了,只是不知这样和直接合并查询相比效率如何,会不会慢一些

  • 阿森纳里 数据从业者,PBI爱好者
    2020-01-21 14:43:36

    跟着几位大神学到了不少,不过还有一个疑问,不知大神们为什么都喜欢用[a=x1,b=x2][b]这样的写法,为什么不用let a=x1, b=x2 in b这样的写法呢,是不是用构造Record效率更高一些呢

  • xiaoni
    2020-02-10 21:15:54

    List.Mode(
    List.ReplaceRange(Table.ToRecords(奖金),3,0,{x}),
    each [姓名] ) &x当众数后连接个x为什么把其他替换掉了呢?这个我不明白,请指教

  • 阿森纳里 数据从业者,PBI爱好者
    2020-02-10 21:45:25

    @xiaoni 这个x代表姓名表中的每一个姓名,我的理解是在奖金表转换为的record构成的list的最后插入一个比如[姓名="dq"](这就是x)的元素,然后找到姓名出现最多的那个元素,每个元素都只出现了一次,因此取最后一个元素[姓名="其他", 奖项="参与奖", 奖金=500],然后在后面跟一个[姓名="dq"],也就重新定义了record中的姓名字段,把字段值从"其他"变为了"dq"

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