Power Query 应用案例——旅行社的行程安排表

团队表:

团队表

行程表:

行程表

目标表:

目标表

要求注意事项:

  • 结果表的日期需要连续
  • 日期列的最后一行只需要列出最后一天行程日期
  • 数据可进行灵活扩展变动

(一) 思路分析:

制作这样的表格,可以通过多次合并查询来进行,例如2个表

样例表1

样例表2

通过一个共享日期列进行合并查询后就可以得到这样个效果。

样例结果

我们围绕这个最终实现效果来进行数据的整理。

(二) 数据要求及构成方式

1. 日期列构成

  • 因为是一个连续的,所以可以用List.Dates构建。
  • 最大日期是团队最后行程日,所以需要求出每个团的行程日期,并求得最大值。

    2. 标题列构成

  • 求得团队的数量来确定标题列的数量。
  • 团队的数量需要通过重复拆分来得到

    3. 团队数据构成

    我们需要构建成这样一张表,通过生成多张类似的表即可完成最后的合并。

样例表1

(三) 实际操作

1. 日期列构成操作

各个团最后一天日期
例如:2016/1/6日有一个2日团队,所以团队最后一个行程日期为2016/1/6+1=2016/1/7

A. 表转换为列

在日期团队表中进行处理

Table.ToColumns(更改的类型)

团队表_转换列

B. 找到数字所在的位置(也就是行程起始日的位置)

List.Transform(List.Skip(拆分到列,1), (a)=>    //List.Skip是跳过日期列,我们只需要求后面团队的起始日期值
                                     List.PositionOfAny(a, {1..2},1)  //第3参数是1所以返回的是最后一个匹配值的位置
                )

其中{1..2}的2可以使用List.Max(a)作为变量进行

团队表_标题日所处位置

C. 求出对应的最后一天的起始日期值

List.Transform(最后位置, each 更改的类型[日期]{_})

团队表_最后行程起始日

D. 求出最晚行程日期

List.Max(List.Transform({1..Table.ColumnCount(更改的类型)-1}, //生成列表{1..3}
                                            //因为日期相加需要用到duration
                          each 自定义4{_-1}+ #duration(     //自定义4代表的是日程的起始日期列表
                                             //找到标题第1个数字代表所需要的行程日期,并转换用于计算
                                                        Number.From(Text.Start(
                                                                               Table.ColumnNames(更改的类型){_},1))-1, 0, 0, 0 
                                                       )
                        )
          )

解释:
求得通过行程日期的初始值+行程日期的天数,求出行程最晚日期用于生产日期列。这里只能计算行程日期为10日以内的,如果是2位数,则需要先提取数字在合并,或者判断第一个非数字的位置再提取。

E. 生成日期列并转换成表

List.Dates(List.Min(更改的类型[日期]),
           Number.From(行程最晚日期-List.Min(更改的类型[日期]))+1,
           #duration(1,0,0,0)
          )

解释:
我们生产了从起始日常到最终结束行程的整个日期表,后期可以通过合并查询来取得最终的表格。

2. 标题列处理

A. 通过逆透视及字符替换达到如下效果(更改属性列的值以便用于匹配)

行程表_逆透视

B. 把值为非1的根据值来进行重复,通过添加列重复值并展开。

List.Repeat({1},[值])

行程表_重复团队展开

C. 根据属性列去匹配

团队表_匹配行程表

D. 匹配对应日期团所对应的行程日期列

List.Dates([日期],
           Number.From(Text.Start([属性],1)), 获取标题中数字天数并转换成数值
           #duration(1,0,0,0)  //按天增加
          )

E. 提取行程表里的值

团队表_匹配日期

F. 把生成的日期列和对应的行程列转换成表

Table.FromColumns({[自1],[自2]})

G. 把对应表里面的属性转换成团+对应的团队数

团队表_匹配行程编号

3. 日期列标题列内容合并

A. 通过批量合并查询

List.Accumulate({0..Table.RowCount(团队表)-1},  //循环次数
                 转换为表,  //为通过处理后的日期表
                (x,y)=>Table.NestedJoin(x,    
                                       {"Column1"},  //日期表的日期列     
                                       团队表[处理后行程]{y},  //是改过标题的那个带有日历的表
                                       {"Column1"},  //行程表的日期列
                                       Text.From(y), //转换文本成为标题列名
                                       JoinKind.LeftOuter //左外部合并
                                       )
               )

行程日期匹配

B. 最后批量展开并排序时间列即可

旅行社日期行程表