cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Find the match between two lists and columns

I have two lists or columns. One with the answers made by some people (LIST_1) and the second with the correct alternatives (LIST_2). How can I, in POWER QUERY, return the number of lines that match?

 

I speak list or column, because I would imagine that finding the lines that coincide as a list would be the best way. But, if possible as a column, it can also be useful.

 

SampleSample

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier
Solution Supplier

Re: Find the match between two lists and columns

Hi, @PietroFarias 

let
   List_1 = {"E", "B", "C", "B", "E", "B", "C", "E", "E", "C", "A", "E"},
   List_2 = {"A", "A", "A", "B", "C", "D", "E", "B", "D", "C", "A", "B"}
in List.Count(List.Select(List.Zip({List_1,List_2}),each _{0}=_{1}))

If my code solves your problem, mark it as a solution

ziying35

View solution in original post

4 REPLIES 4
Super User V
Super User V

Re: Find the match between two lists and columns

Hi @PietroFarias ,

 

Try this m code:

 

let
List_1 = {"E", "B", "C", "B", "E", "B", "C", "E", "E", "C", "A", "E"},
List_2 = {"A", "A", "A", "B", "C", "D", "E", "B", "D", "C", "A", "B"},
Match = Table.RowCount(Table.SelectRows(Table.FromColumns({List_1, List_2}), each [Column1] = [Column2]))
in
Match

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
Solution Supplier
Solution Supplier

Re: Find the match between two lists and columns

Hi, @PietroFarias 

let
   List_1 = {"E", "B", "C", "B", "E", "B", "C", "E", "E", "C", "A", "E"},
   List_2 = {"A", "A", "A", "B", "C", "D", "E", "B", "D", "C", "A", "B"}
in List.Count(List.Select(List.Zip({List_1,List_2}),each _{0}=_{1}))

If my code solves your problem, mark it as a solution

ziying35

View solution in original post

Highlighted
Resolver II
Resolver II

Re: Find the match between two lists and columns

Wow!! I didn't know List.Zip.

 

Thanks @ziying35 !

Highlighted
Resolver II
Resolver II

Re: Find the match between two lists and columns

Hey @PietroFarias ,

 

We can achieve this using this simple technique also:

 

Exepected Output:

 

matching.JPG

 

Step1 : Add a conditional column

 

Enter the details as shown in the pictureEnter the details as shown in the picture

 

  • New column name= Result
  • If Column Name = Students Answers (Column)
  • Operator= Equals
  •  
    Value= Correct Answers (Column)

    In the value's list Select a columnIn the value's list Select a column

  • Output = Correct
  • Else = Not Correct

Then Click on OK

 

 

 

ResultResult

 

If your problem is solved then please accept this as solution.

 

Thank you

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors