excel 在PowerQuery M代码中使用另一个列表过滤列表

5jvtdoz2 于 2023-03-04 发布在其他

关注(0)|答案(4)|浏览(145)

我有一个列名列表。我想使用另一个列表中的项来筛选该列表。我想返回包含myFilter列表中任何项的所有列名。如果筛选器是文本项，我可以创建筛选器，但我无法遍历每个项并返回列表。

ColumnNames= {"E ID", "W ID", "P Chemical", "XIC","YFE","ZIC"}
myFilter = {"W ID","IC"}

结果应为：

Result = {"W ID","P Chemical", "XIC", "ZIC"}

由于IC包含在Chemical、XIC和ZIC中，因此W ID完全匹配。
我想应该是这样的：

List.Select(ColumnNames, each Text.Contains(_, each myFilter{_}))

因为它和这个

List.Select(ColumnNames,each Text.Contains(_,"W ID"))

但我无法得到正确的代码，我感谢帮助。

excel

来源：https://stackoverflow.com/questions/75532923/filter-a-list-by-using-another-list-in-powerquery-m-code

4条答案

按热度按时间

wtzytmuj1#

我会这样做。

let
    columnNames= {"E ID", "W ID", "P Chemical", "XIC","YFE","ZIC"},
    #"Converted to Table" = Table.FromList(columnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "columnNames"}}),
    myFilter = {"W ID","IC"},
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "filter", each myFilter),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "filter"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "match", each if Text.Contains([columnNames], [filter], Comparer.OrdinalIgnoreCase) then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [match] = true)[columnNames]
in
    #"Filtered Rows"

赞(0）回复(0）举报 2023-03-04

eagi6jfj2#

结账

List.Select(ColumnNames, each List.Contains(myFilter , _))

赞(0）回复(0）举报 2023-03-04

6pp0gazn3#

可以使用集合函数，如intersect。

= List.Intersect({ColumnNames, myFilter})


let
   ColumnNames= {"E ID", "W ID", "P Chemical", "XIC","YFE","ZIC"},
    myFilter = {"W ID","IC"},
    result = List.Intersect({ColumnNames, myFilter})
in
    result

赞(0）回复(0）举报 2023-03-04

c3frrgcw4#

为了好玩

let ColumnNames= {"E ID", "W ID", "P Chemical", "XIC","YFE","ZIC"},
myFilter = {"W ID","IC"},
Part1=List.Transform(ColumnNames, each let q=_ in try Table.SelectRows (Table.FromList(myFilter), each Text.Contains(q,[Column1], Comparer.OrdinalIgnoreCase)){0}[Column1] otherwise null),
Part2= List.RemoveNulls(List.Transform(List.Positions(ColumnNames),each if Part1{_}=null then null else ColumnNames{_}))
in Part2

赞(0）回复(0）举报 2023-03-04

我来回答

excel 在PowerQuery M代码中使用另一个列表过滤列表

4条答案

相关问题

热门标签

最新问答