时间戳

首页 > 解决方案 > PowerQuery - 在列表中搜索多个首字母缩写词

powerquery - PowerQuery - 在列表中搜索多个首字母缩写词

问题描述

我想知道在 Power BI M 中是否有可能：我有一个首字母缩略词列表：“AAA、BBB、XXXX、YYY...” 源数据表有一个文本字段（描述），并且可以在一个自由文本。像“Luctus aa quam AAA gravida cum a YYY elementum potenti a ultrices p”之类的东西。我需要选择具有一个或多个首字母缩写词的所有记录，并生成额外的自定义列，该列将列出特定记录中的所有事件（“AAA，YYY ...”）。这是 Excel VBA 中的几行代码，但我需要在 PowerQuery 或 Power BI 中完成。

标签： powerquery

解决方案

你可以试试这个

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DYhBCsAgDMC+UnreJ3rfB0Q8lK4MQZ3Tdu9fySVJzni6mG/g4HXuQERwL/7qxSDeY6eUQJt2HRY9Hwup8b3ZqqIbJpYjo+k2LOUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ad", each _, type table [Column1=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad],
#"Added Custom" = Table.AddColumn(x, "Custom", each {"AAA", "BBB", "XXXX", "YYY"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Text.Contains([Column1],[Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Custom", "Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each #"Filtered Rows"[Custom]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Custom = #"Extracted Values"[Custom]
in
    Custom),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each (List.IsEmpty([Custom])=false)),
    #"Extracted Values" = Table.TransformColumns(#"Filtered Rows", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"

推荐阅读