Power Query - Count # of items in lookup table by column header

Question

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 a screenshot and sample workbook. Any help/guidance/assistance would be ~greatly~ appreciated.

https://drive.google.com/file/d/1VpgwySRra9-gStlmtjJum3w2JySPyrt5/view?usp=sharing

Thank you!

horseyride horseyride · Accepted Answer · 2021-08-10T11:55:51

Load invalid Value, add index, click the index and unpivot other columns. Close and Load. Lets call this Table2

Load Input Data, add index, click the index and unpivot other columns. Merge in Table2 by matching Attribute and Value with left outer join. Expand the resulting column to extract index column from Table2. Filter out the nulls. Group and Count on Attribute.

Table2 = Invalid Values code

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value")
in  #"Unpivoted Other Columns"

Table 1 = Input Data code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Value", "Attribute"},Table2,{"Value", "Attribute"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Index"}, {"Index.1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Index.1] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute"}, {{"Invalid Entry Count", each Table.RowCount(Table.Distinct(_)), type number}})
in  #"Grouped Rows"

Power Query - Count # of items in lookup table by column header

1 Answers