如何在Powerquery中的整个表中搜索短语

Przemyslaw Remin

如果整个表在任何列或任何行中都包含搜索值，则如何检入powerquery。说我们有一张桌子：

+------------+-----------+---------+
|  column1   |  column2  | column3 |
+------------+-----------+---------+
| apple      | banana    | cherry  |
| damson     | entawak   | fig     |
| watermelon | wolfberry | plum    |
+------------+-----------+---------+

我想找到哪一列和哪一行wolfberry？它是c = 2和r = 3。我想将坐标用于进一步的数据处理。

这是示例表的代码，为您提供方便：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFKSswDQiAjOSO1qKhSKVYnWiklMbc4Pw8olppXkliemH1oAZCdlpkOlixPLEktyk3NASsoz89JSwLr01EqyCnNVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
    #"Changed Type"

阿列克谢·志古林

您可以使用以下技术：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFKSswDQiAjOSO1qKhSKVYnWiklMbc4Pw8olppXkliemH1oAZCdlpkOlixPLEktyk3NASsoz89JSwLr01EqyCnNVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    func = (word) =>
            [r = Table.AddIndexColumn(Source, "r", 1, 1),
             c = Table.AddIndexColumn(Table.Transpose(Source), "c", 1, 1),
             coordinates = [r = Table.FindText(r, word)[r]{0}, c = Table.FindText(c, word)[c]{0}]
            ][coordinates],
    search = func("wolfberry")
in
    search

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。