Hello -
I've spent hours and hours on this, but no luck. Can anyone help?
Issue: I have two tables; one source data and one lookup data. I need to count the number of times the lookup data appears in the corresponding column on the main table. The column names could be anything, but they always have a counterpart in the other table.
In the attached example, There are four columns (Month/Shape/Animal/Mineral) in the first table, and the same columns in the lookup table. The lookup table contains items that do not belong in the respective columns (i.e. "Triangle" is not a month, and "Kitten" is not a shape).
I'm trying to use Power Query to count the number of invalid entries in each column:
Input Data
-Month- -Shape- -Animal- -Mineral-
January Square Puppy Gold
February Circle Kitten Quartz
Square Triangle October Amber
Circle Kitten October Lead
Square Puppy Horse Gold
Circle Puppy Goldfish Silver
May Hexagon Pig Gold
Invalid Values
-Month- -Shape- -Animal- -Mineral-
Square Kitten July Kitten
Circle Puppy October Puppy
Triangle Pig January Pig
Trying to end-up with this table:
Field Invalid Entry Count
-Month- 4
-Shape- 3
-Animal- 2
-Mineral- 0
I have also attached a sample workbook. Any help/guidance/assistance would be ~greatly~ appreciated.
Thank you!
Bookmarks