☰

在 powerquery 中重新格式化人员规划表

tag-icon • tag-icon microsoft-excel power-query
在 powerquery 中重新格式化人员规划表

我有一张桌子：

日期 人A 人B 人C
1/1/24 X H
2/1/24 X 是
3/1/24 A X
4/1/24 是 X
5/1/24 X X

在表格上方，我有一张标题为“Person”的表格，其中有一个单元格带有下拉菜单，可以选择 PersonA-PersonB-PersonC。

我想创建一个新表，其中包含特定人的所有日期，其中值为 X 或 Y（忽略其他值）。
因此输出是每个字母一行，并包含所有相应的日期。

人 信 日期 日期 日期
人A X 2024 年 1 月 1 日 2024 年 5 月 1 日 ...
人A 是 2024 年 4 月 1 日

我已经为该人员创建了一个数据源，其中包含人员列表。
我不知道如何使用值过滤列“table_person”。
之后的步骤同样超出了我的知识范围。我希望删除“不需要的”列，区分 X 和 Y，取消透视表并以某种方式转置它。

答案1

从表中“获取数据”，按日期排序（以防您的数据尚未在表中排序），将日期列的数据类型设置为文本，然后在该列上使用提取>分隔符前的文本并选择“空格”以丢弃时间组件。

右键单击日期列并选择“取消透视其他列”。这将输出三列，一列用于日期，一列用于人员（默认情况下列名为“属性”，但您可以在公式栏中更改它），第三列名为“值”，其中包含您的 X、Y 和 Z - 您也可以重命名它。

过滤人员列，查找您作为参数引入的人员。过滤 X 或 Y 的值（如果可能存在 x 和 X，请注意区分大小写）。

现在来介绍技巧 - 对 Person 和 Letter 执行 Group By，并为 Date 列选择任意聚合，例如 Sum（将新列命名为“Dates”之类的名称）。这不起作用，但您随后会修复它。在公式栏中，将 List.Sum 替换为 Text.Combine，并添加分隔符，例如分号：

= Table.Group(#"Filtered Rows", {"Person", "Letter"}, {{"Dates", each List.Sum([Date]), type text}})

变成

= Table.Group(#"Grouped Rows1", {"Person", "Letter"}, {{"Dates", each Text.Combine([Date],";"), type nullable date}})

剩下的就是用分号分隔符来分割新列。在我的代码中，我没有按人进行过滤，我只是保留了所有结果并在最后应用了合理的排序顺序。

我为整个过程编写的代码，假设您的数据从名为 Table1 的表开始（只需根据需要进行调整）：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows by date" = Table.Sort(Source,{{"Date", Order.Ascending}}),
    #"Changed Type Date to Text" = Table.TransformColumnTypes(#"Sorted Rows by date",{{"Date", type text}, {"personA", type text}, {"personB", type text}, {"personC", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type Date to Text", {{"Date", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Extracted Text Before Delimiter", {"Date"}, "Person", "Letter"),
    #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Letter", Text.Upper, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each ([Letter] = "X" or [Letter] = "Y")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Person", "Letter"}, {{"Dates", each Text.Combine([Date],";"), type nullable date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Grouped Rows", {{"Dates", type text}}, "en-GB"), "Dates", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
    #"Sorted Rows by Person and Letter" = Table.Sort(#"Split Column by Delimiter",{{"Person", Order.Ascending}, {"Letter", Order.Ascending}}),
    #"Changed Type back to Date" = Table.TransformColumnTypes(#"Sorted Rows by Person and Letter",{{"Dates.1", type date}, {"Dates.2", type date}, {"Dates.3", type date}})
in
    #"Changed Type back to Date"

相关内容