Hola People, time and again I stumble into interesting problems that are worth your try!
Dynamic Column Calculations – Power Query Challenge
Consider this Data
- Apart from the standard sales columns the above data has 3 Tax Columns with values 1 or 0.
- 1 Means tax is applicable.
- 0 Means tax is not applicable.
The obvious next question is, how much is the tax rate? Which is given in a separate Tax Table below.
Expected Output
You mission, should you choose to accept it, is to calculate Total Tax Column.
Total Tax = Applicable Tax (from Sales Data) x Tax Rate (from Tax Table) x Value (from Sales Data)
These constrains should not break your solution!
- New (non tax) columns may be added in the Sales Data.
- New Tax columns may be added or could change their name but will retain the string “Tax”.
- Columns in the Tax Table and Tax columns in Sales Data will always have same names.
- For the bold hearted 💪🏼 – Try not using the Pivot or Unpivot feature in your Solution.
Post your answer in the comments. Good luck proverbial “Ethan” 😉
The solutions will self appear on this page in 5 days!
