Filter a list by using another list in PowerQuery M code

0 votes

List of column names that I have. I want to use the elements from another list to filter the current list. Any column names that contain any of the items in the myFilter List should be returned, if possible. I can create the filter if the filter is a text item, but I was unable to walk through each item and produce a list.

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

The result should be:

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

As IC is contained in Chemical, XIC and ZIC, and W ID is a full match.

I imagine it should be something like this:

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

Because it works with this

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

But I cannot get the right code. Can someone please help me with this?

Feb 23, 2023 in Others by narikkadan
• 63,720 points
1,683 views

1 answer to this question.

0 votes

Try this:

enter image description here

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"
answered Feb 23, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,720 points
593 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,720 points
3,395 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,720 points
910 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,371 views
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
762 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,007 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
6,331 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP