☰

如何将日期值索引到 Excel 中的列中

tag-icon • tag-icon microsoft-excel pivot-table power-query
如何将日期值索引到 Excel 中的列中

我在 Excel 2016 中有下表：

ID  Type1            Type2            Type3  
1   01.03.2018       null             01.02.2019
2   01.04.2018       01.05.2018       null
3   null             01.03.2018       01.05.2018

现在我尝试将这些值索引到新表中，该表应如下所示：

ID  01.02.2018       01.03.2018       01.04.2018    01.05.2018     
1   Type3            Type1            null          null
2   null             null             Type1         Type2
3   null             Type2            null          Type3

实现这一目标的最佳方法是什么？使用 Power Query 还是使用 Excel 函数？我该如何实现这种转变？

答案1

您可以在 Power Query 中完成所有这些操作

  • 选择ID列并Unpivot other columns
  • 可选地，列Sort中的行Value
  • 在列上进行透视Value，将“值”列设置为Attribute，并选择“高级选项”Don't Aggregate

在此处输入图片描述

就是这样！

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type1", type date}, {"Type2", type date}, {"Type3", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Value", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Value", type text}}, "en-US")[Value]), "Value", "Attribute")
in
    #"Pivoted Column"

源数据

在此处输入图片描述

结果

在此处输入图片描述

无效的是 M 中的保留字，因此如果您需要显示实际的单词“null”，则需要将其替换null为使其成为字符串的内容。您可以使用诸如#(00A0)null在 null 前加上 NBSP 或#(200B)nullZWSP（零宽度空格）之类的东西。并且您可能还有其他技巧可以使用。

答案2

我将使用 Power Query 和 Power Pivot（Excel 数据模型）的组合来满足这一要求。

首先，我要创建一个 Power Query。选择 ID 列，然后使用“取消透视其他列”功能将数据转换为属性（输入列名称，例如 Type1）和价值（输入单元格值，例如 01.02.2018）。我将设置查询以加载到数据模型（Power Pivot）中。

然后我将基于数据模型创建一个数据透视表，使用价值行列和属性单元格列。

您可以重命名生成的列以适应需要。我更喜欢在 Power Query 中执行此操作，以获得更好的透明度。

相关内容