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

团队表：

行程表：

目标表：

要求注意事项：

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

(一) 思路分析：

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

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

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

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

1. 日期列构成

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

  2. 标题列构成

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

  3. 团队数据构成

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

(三) 实际操作

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. 最后批量展开并排序时间列即可