cancel
Showing results for
Did you mean: Frequent Visitor

## Excel formulas in Power Query

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
1 ACCEPTED SOLUTION  Super User

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}}),
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

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  Super User

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}}),
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

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   