excel - 如何使用 Power Query 在一行中删除重复项？

Question

我的数据如下所示:


金属丝
点1
点2
第3点
第4点
第5点
第6点


一个
WP1
WP1
WP2

WP2

乙
WP3
WP4
WP3

WP4

C
WP5
WP5
WP6
WP7
WP6
WP7


(注意每行的不同长度和重复项)
我希望最终结果是:


金属丝
点1
点2
第3点


一个
WP1
WP2

乙
WP3
WP4

C
WP5
WP6
WP7


删除了重复项，并删除了空格。
这与 =UNIQUE() 函数非常相似，但在电源查询中不可用。

Answer 1

使用列要容易得多，因此我建议取消旋转 Point 列，删除重复项，然后将其放入您想要的形状。
这是一个完整的查询，您可以将其粘贴到高级编辑器中，以便更仔细地查看每个步骤:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoPMEQijYAkBIHYsTrRSk5gtjGYNIGzYWpMwGqcwWxTJNIMTJqjsGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wire = _t, Point1 = _t, Point2 = _t, Point3 = _t, Point4 = _t, Point5 = _t, Point6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wire", type text}, {"Point1", type text}, {"Point2", type text}, {"Point3", type text}, {"Point4", type text}, {"Point5", type text}, {"Point6", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Wire"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Wire", "Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Wire"}, {{"Point", each Text.Combine([Value],","), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Point", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Point1", "Point2", "Point3"})
in
    #"Split Column by Delimiter"