Excel Power Query: Using List.MatchAny on a column value

0 votes

In Excel Power Query, I have a table. Column A has single numbers. I want to mark those records where the Column A value matches a list. A cutdown version of the problem is:

let
    TableA = Table.FromColumns({{1,2,4}}, {"A"}),
    ListB = {4,5,6 },
    DPart = Table.AddColumn(TableA, "IsInB", 
            List.MatchesAny(ListB, each _ = [A]))
in
    DPart

I get an error in the DPart line

Expression.Error: We cannot apply field access to the type Number.
Details:
  Value=4
  Key=A

Apparently, the code is trying to access the [A] column of elements of the list, instead of the [A] column of TableA.

What I'm looking for, is the correct syntax to accomplish this.

Oct 22, 2018 in Power BI by Upasana
• 8,550 points
311 views

1 answer to this question.

0 votes

try this.

let
    TableA = Table.FromColumns({{1,2,4}}, {"A"}),
    ListB = {4,5,6 },
    DPart = Table.AddColumn(TableA, "IsInB", 
            (x) => List.MatchesAny(ListB, each _ = x[A]))
in
    DPart

But I would prefer:

let
    TableA = Table.FromColumns({{1,2,4}}, {"A"}),
    ListB = {4,5,6 },
    DPart = Table.AddColumn(TableA, "IsInB", 
            each List.Contains(ListB, _[A]))
in
    DPart
answered Oct 22, 2018 by Annie97
• 2,190 points

Related Questions In Power BI

0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18 in Power BI by Cherukuri
• 31,840 points
1,056 views
0 votes
1 answer

How to rename a column using DAX in Power BI?

Hi Ramya, I found these two function that ...READ MORE

answered Aug 21 in Power BI by anonymous
• 31,840 points
331 views
0 votes
1 answer

Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 42,510 points
89 views
0 votes
1 answer

What are the components I need to excel to become a pro in power BI

Having knowledge about these topics is a must.  Power ...READ MORE

answered Oct 8, 2018 in Power BI by Kalgi
• 42,510 points
64 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14 in Power BI by Upasana
• 8,550 points
35 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,190 points
493 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
202 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,310 points
188 views
0 votes
1 answer

Passing a parameter to Power BI Embedded via embedded URL

Add the following to the end of ...READ MORE

answered Oct 23, 2018 in Power BI by Annie97
• 2,190 points
1,291 views