Here comes Power Query Challenge 2. The Subjects table has list of subjects. The words are separated by either comma or space and amount of space is also not uniform. The case of subjects is also not uniform.
The count of rows for subjects is needed. Hence, if a subject appears more than one time in a row that count is treated as 1 as it appears in the same row. Taking the example of Physics in second row, it appears 2 times. But for that row, its count should be taken as 1. In 3rd row, it appear once only. In 4th row, it appears 2 times, but count is 1 only for that row.
Hence total count for Physics is 3.
Try to do maximum steps through UI and minimize writing formulas / M-code.
Of course, the query should be dynamic i.e. even if number of words increase or decrease in a line OR/AND number of rows increase or decrease, the Query should give right result on Refresh.
The problem file related to this challenge can be downloaded from PQ_Challenge_2_Problem
The solution to Power Query Challenge 2 will be published after a week i.e. on Tuesday, 12-Oct-21.
Solution link – Solutions to Power Query Challenges – 1 to 7
Power Query M function reference – PowerQuery M | Microsoft Docs