时间戳

首页 > 解决方案 > 使用 powerquery 将具有多列的表还原为具有两列的表，这些列表示原始表中的日期对？

pivot-table - 使用 powerquery 将具有多列的表还原为具有两列的表，这些列表示原始表中的日期对？

问题描述

想象一下，我有一个“水平”数据集，其中包含：

  1. 唯一键
  2. 跨多个列的多个“对”日期（即事件 A 开始、事件 B 开始、事件 C 开始等，以及事件 A 结束、事件 B 结束、事件 C 结束等的单独列）。
  3. 特定“事件”的单个日期（不是一对）。

本质上，看起来像这样：

数据集

唯一键 事件 A 开始 活动结束 单日活动 事件 B 开始 活动 B 结束 第二次单身约会活动
键 1 2021 年 1 月 1 日 2021 年 1 月 3 日 2021 年 1 月 2 日 2021 年 1 月 5 日 2021 年 1 月 10 日 2021 年 1 月 10 日
关键 2 2021 年 1 月 7 日 2021 年 1 月 10 日 无效的 无效的 无效的 无效的

如何使用 PowerQuery 将上面的数据集转换为这样的表？

预期输出：

唯一键 事件 开始日期 结束日期
键 1 事件 A 2021 年 1 月 1 日 2021 年 1 月 3 日
键 1 单日活动 无效的 2021 年 1 月 2 日
键 1 事件 B 2021 年 1 月 5 日 2021 年 1 月 10 日
键 1 第二次单身约会活动 无效的 2021 年 1 月 10 日
关键 2 事件 A 2021 年 1 月 7 日 2021 年 1 月 10 日

我试过了：

我觉得我肯定做错了什么，所以在这里询问是否可以根据输入数据使用 PowerQuery 实现我想要的输出？

标签： pivot-tablepowerqueryunpivot

解决方案

你肯定需要在 unpivot 之上做一些额外的工作。

这是我的处理方法：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k6tVDBU0lEyVPBKzFMwMjACcYyROUbIHFNkjqEBLl6sDsRkI6C4OW4teaU5Odip2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Key" = _t, #"Event A Start" = _t, #"Event A End" = _t, #"Single Date Event" = _t, #"Event B Start" = _t, #"Event B End" = _t, #"2nd Single Date Event" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Unique Key"}, "Event", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Event], "Start") then "Start Date" else "End Date"),
    #"Transformed Text" = Table.TransformColumns(#"Added Custom",{{"Event", each if Text.EndsWith(_, "Start") or Text.EndsWith(_, "End") then Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}) else _, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Transformed Text", List.Distinct(#"Transformed Text"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

脚步：

  1. 取消透视日期列
  2. 添加一个新列以将每一行标记为开始日期/结束日期
  3. 去掉 [Event] 列中的“Start”/“End”后缀
  4. 透视第 2 步中的新列

推荐阅读