Power Query - Count of items by column header

[Trioptre]

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. I'm thinking that "Group By" is the way to go, but I'm still stuck....

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 link to my sample workbook as well as a screenshot illustrating my issue. Any help/guidance/assistance would be ~greatly~ appreciated.

Example - Invalid Data Count.xlsx

drive.google.com

Thank you!

 

[billszysz]

Your Merge1 query might look like the one below

let
    Source = Table.NestedJoin(Invalid_by_Field, {"Attribute", "Value"}, Raw_Data, {"Attribute", "Value"}, "Raw_Data", JoinKind.LeftOuter),
    Transform = Table.TransformColumns(Source, {{"Raw_Data", Table.RowCount}}),
    #"Grouped Rows" = Table.Group(Transform, {"Attribute"}, {{"Sum", each List.Sum([Raw_Data]), type number}})
in
    #"Grouped Rows"