Dynamic Column Calculations in Power Query | 4 Crazy Solutions

In this video, we'll explore the 4 awesome solutions from the last Power Query Challenge.

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== CHAPTERS =====
0:00 Intro
0:26 Revisiting the Problem
1:52 Understanding Record.Field Function
3:45 Solution 1 - Victor
6:26 Solution 2 - mma173
8:52 Solution 3 - Garucia
10:06 Solution 4 - Mohammed
13:23 Chandeep's Solution
15:14 Shoutout & My Courses

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[Excelambda]

PQ rocks!!
Only if I'm allowed, a formula alternative solution, no PQ, just to have an idea ( if sales data B1:G15 and tax table I2:K2)
- main calculation
=BYROW(D2:F11*I2:K2*G2:G11, LAMBDA(x, SUM(x)))
- single cell formula including all cosmetics
=LET(t, B1:G11, x, I2:K2,
w, SEQUENCE(ROWS(t)),
b, IFERROR(BYROW(INDEX(t, w, {3, 4, 5})*x*INDEX(t, w, 6), LAMBDA(x, SUM(x))), "Total Tax"),
HSTACK(INDEX(t, w, {1, 2, 6}), b)
)
Needless to say, sales data can be regular array or table, if table, no refresh ever needed when table receives more data. ✌

[JR-lybx]

= Table.AddColumn(#"Changed Type", "TotalTaxes",
each
Tax[Tax]{0}* [Tax]*[Value] +
Tax[Tax2]{0}* [Tax2]*[Value] +
Tax[Tax3]{0}* [Tax3]*[Value] )

[IvanCortinas_ES]

An excellent challenge. Thank you for the solutions!

[gennarocimmino]

Hi Chandeep,
Always grateful for your publications, let me ask:
Why in the description of your videos, if the topic has been covered in your blog (as in this case) do you not also add the link to the blog page?
Your blogs are very rich in information and failure to include the link could prevent your followers from gaining full knowledge of the topics you share on youtube than on your blog.
Sincerely

[odel_leal]

Great Work!! All the contibutors. They are Amazing!!!

[sohailansari]

Hello chandeep sir, great video, I was wondering if we can use "Dynamic M Parameters" to pull the Data within Power BI using SQL Store Procedure, I want to pass SP parameters via Dynamic M query parameters, please assist me any blog would also be fine. Thanks🙏

[kommabalaji]

Sir Kindly share any YTD/ blogs url links related to Power BI realtime DAX scenarios
kindly make videos on the same. (for practice purpose)

[nevenmesic]

Hi is there a way to separate value of one row into two separate row values where one row is 25% of the value and one cost category while second cost category is 75% of original value...i have solved the problem with 2 separate queryes and appended them with original query where i have filterted the rows so not to duple them..but i assume there is a better solution hehe

[jerrydellasala]

Wish you had posted the code in the blog.

[GeertDelmulle]

The first solution is my favourite. It feels like the least nested one.
BTW: perhaps you can time these solutions for computational efficiency.

[williamarthur]

Loved this solution, one question foy anyone, if you change the zero's to null, the method fails
and any row containing a null will return null, can anyone explain or direct me some information as to why this is.

[McIlravyInc]

Why calculate the zeros at all? If I was doing this math manually I would ignore them and use a formula to do all calcs at once left to right on the calculator... I would think fewer calcs is more efficient but I am new at all this so probably missing something. Thus the question! Thanks.

[jerrydellasala]

Just watched this again and understand it better, but one thing strikes me (aside from your code missing!). None of the solutions is right! None of them rounded down the Total Tax column to one decimal point as presented in the Expected Output, and as a result, all of the solutions are off by 0.007. Now that might not seem like a lot, but if the Value column is in Millions, the Sum of the solutions Total Tax column is 53.803, however when the values are rounded down in Power Query (as they should be if one decimal was Expected Output), the Sum of the Total Tax is 53.9
If the numbers were millions, the underpayment would be $97, 000! Sorry, but Mike at ExcelIsFun drilled in how important it was to make accurate calculations. I do understand that this was an exercise in using Power Query, but frankly a 7 step script using simple if/then clauses was much easier to follow, and produced accurate result is what would be required in the real world.
Disappointed that only 2 of the posted results actually answered correctly with rounded results - Kgas S (also first post), and CA Satish Panchal.

[joncannaday]

Just to test out the lambda formula


Then enter the binary indicator tax columns & values column in the formula