团队表：
团队表
行程表：
行程表
目标表：
目标表
要求注意事项：
- 结果表的日期需要连续
- 日期列的最后一行只需要列出最后一天行程日期
- 数据可进行灵活扩展变动
(一) 思路分析：
制作这样的表格，可以通过多次合并查询来进行，例如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. 最后批量展开并排序时间列即可
旅行社日期行程表
喜欢的请点个赞！
网友评论