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,620 points
4,005 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,160 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, 2019 in Power BI by Cherukuri
• 33,030 points
7,842 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 22, 2019 in Power BI by anonymous
• 33,030 points

edited Dec 31, 2021 by Soumya 34,891 views
0 votes
0 answers

How to work on existing power query using python

Hi I have an existing power query created ...READ MORE

Feb 7, 2020 in Power BI by Biswajith
• 120 points
2,628 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
• 52,360 points
781 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, 2019 in Power BI by Upasana
• 8,620 points
1,521 views
0 votes
1 answer

Format Data to tabular structure in Excel - Power m Query - Excel

Hi, @Kiranbvsn You can check this out: https://www.howtoexcel.org/ ...READ MORE

answered Oct 27, 2020 in Power BI by Gitika
• 65,910 points
501 views
0 votes
1 answer

Power query to return true / false if multiple columns are either = or null

Add column, custom column with formula: = try ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,720 points
1,966 views
0 votes
1 answer

Name not recognized error in List.Generate

Try this: let Source = < >, columnsToExpand ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,720 points
557 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,160 points
4,796 views
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