I have a reference table for categories and in that table I would like to populate the total number of items with a serial number falling in to each category.
Each category has an identifier. So the "Apples" category might have "1" as its identifier. Each specific item in my data table has a serial number like "112" or "1514" where the first digit matches the identifier. So the items with serial numbers "112" and "1514" match the identifier "1" so they would both get counted into the "Apples" category.
I want to run through my items and count how many belong to each category, and then populate my reference table so I know that the "Apples" category has, say, 150 items, and then the "Oranges" category has, say, 75 items.
The following code is getting me 90% of the way with a static identifier, "7". But that, of course, just gets me a count for serial numbers starting with a 7, and populates every row with that count. I need a dynamic result.
Table.AddColumn(
#"Add Identifier", "Count", each Table.RowCount(
Table.SelectRows(
Table.SelectColumns(
#"Data", "Serial Number"),
each Text.StartsWith([Serial Number], "7")
)))
Where I'm getting stuck is inserting a dyamic reference. If I modify the code above to have:
each Text.StartsWith([Serial Number], #"Add Identifier"[Identifier])
it begins to return a "List" type value, and throws an error indicating that it cannot convert a "List" type value to "Text" type. My best guess is that it's creating a list which includes the sum of each group.
I feel like there should be a way to do this, perhaps more easily, using Table.Group(), but with either that function, or my above code, my private joke has become that my output is always "Error, cannot convert something."
Can anybody help me figure out what I'm missing so that my columns will populate based off a dynamic reference rather than a static one? I feel like I'm missing a small piece of the puzzle to cross that last mile.
