Excel formulas in Power Query

[zhandos]

Dear community,

Can you please help me to get column "B" results inside Power Query? Formulas are written in Excel

	B	C	formula
2	0		0
3	0		B2
4	10000		(B5-B3)/2+B3
5	20000	20000	B3+C5
6	20000		B5
7	26000		(B8-B6)/2+B6
8	32000	12000	B6+C8

[BA_Pete · Accepted solution]

Hi @zhandos ,

I've done this purely as an exercise, but I don't imagine this will be of any use to you in reality. Paste this over the default code in a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIAYqVYnWglY2SOCZBhbmAA55sCGYYGEAFDEAMsagbimSIpMwcyLJD4FiAFRhABS7CuWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    addCalcs = Table.AddColumn(chgTypes, "calcs", each 
let
  B2 = chgTypes{0}[B],
  B3 = chgTypes{1}[B],
  B4 = chgTypes{2}[B],
  B5 = chgTypes{3}[B],
  B6 = chgTypes{4}[B],
  B7 = chgTypes{5}[B],
  B8 = chgTypes{6}[B],
  C5 = chgTypes{3}[C],
  C8 = chgTypes{6}[C]
in

if [A] = 2 then 0
else if [A] = 3 then B2
else if [A] = 4 then (B5-B3)/2+B3
else if [A] = 5 then B3+C5
else if [A] = 6 then B5
else if [A] = 7 then (B8-B6)/2+B6
else if [A] = 8 then B6+C8
else null)
in
    addCalcs

This is not what Power Query is designed for, you should be using DAX on a properly-structured table for these types of calculations.

Pete