如何在PowerBI (Dax)中正确排序WBS

Question

我有一个来自在这里输入图像描述的WBS，它是文本格式，例如1，1.1，1.1.10，1.1.2 在这里输入图像描述

我不能转换成十进制或整数。

理想情况下，我希望在1.1.10之前对1.1.2进行排序。

Answer 1

您需要通过在Power Query中转换数据来添加排序列。见下面的片段。

​

​

如果您使用下面发布的M代码，您应该能够进行一个空白的查询并粘贴到代码中，以查看我是如何到达排序列的。

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQDQqVYHQjLCM4yhrNM4CxTOMsMzjKHsyzgLEs4y9AAwYTYYgS1zRhur7GeERIL6IJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WBS = _t]),
    #"Duplicate Column: The WBS Column" = Table.DuplicateColumn(Source, "WBS", "WBS - Copy"),
    #"Split Column by Delimiter: Use Period" = Table.SplitColumn(#"Duplicate Column: The WBS Column", "WBS - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"WBS - Copy.1", "WBS - Copy.2", "WBS - Copy.3"}),
    #"Changed Type: Make Split Columns Number Types" = Table.TransformColumnTypes(#"Split Column by Delimiter: Use Period",{{"WBS - Copy.1", Int64.Type}, {"WBS - Copy.2", Int64.Type}, {"WBS - Copy.3", Int64.Type}}),
    #"Sorted Rows: In Order of the WBS Hierarchy" = Table.Sort(#"Changed Type: Make Split Columns Number Types",{{"WBS - Copy.1", Order.Ascending}, {"WBS - Copy.2", Order.Ascending}, {"WBS - Copy.3", Order.Ascending}}),
    #"Added Index: Index Will Serve as Sort" = Table.AddIndexColumn(#"Sorted Rows: In Order of the WBS Hierarchy", "Index", 0, 1, Int64.Type),
    #"Renamed Column: Named Index to WBS Sort" = Table.RenameColumns(#"Added Index: Index Will Serve as Sort",{{"Index", "WBS Sort"}})
in
    #"Renamed Column: Named Index to WBS Sort"

虽然我没有在上面执行这个步骤，但我建议删除三个WBS -Copy.列。在达到排序栏后，它们是无用的。在此之后，继续关闭并在Power Query中应用您的更改。在Power桌面的report视图中，选择您的WBS列。在“列工具”选项卡中选择“按列排序”下拉列表，然后选择新派生的WBS排序列作为排序列。见下面的例子。

​

​

结果：

​

​