Here comes Power Query Challenge 5. Given the height and width, you need to generate the Multiplication Table. In the example. Height is 20 and width is 8, hence multiplication table columns should be from 1 to 8 and each should contain 20 rows.
Of course, the query should be dynamic i.e. if height and width changes, the query should give right result when Refreshed.
Try to do maximum steps through UI and minimize formula / M-code.
The problem file related to this challenge can be downloaded from PQ_Challenge_5_Problem
The solution to Power Query Challenge 5 will be published after a week i.e. on Friday, 15-Oct-21.
Solution link – Solutions to Power Query Challenges – 1 to 7
Power Query M function reference – PowerQuery M | Microsoft Docs
I do not use Power Query but thought this might be an interesting VBA challenge. What I came up with is this Change event code procedure…
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) Like "[AB]2" Then
Application.EnableEvents = False
[D2].CurrentRegion.Clear
If [COUNT(A2:B2)=2] Then [D2].Resize([A2], [B2]) = _
Evaluate("ROW(1:" & [A2] & ")*TRANSPOSE(ROW(1:" & [B2] & "))")
Application.EnableEvents = True
End If
End Sub
That's a fantastic approach!!!