cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zhandos
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

 

 BCformula
20 0
30 B2
410000 (B5-B3)/2+B3
52000020000B3+C5
620000 B5
726000 (B8-B6)/2+B6
83200012000B6+C8
1 ACCEPTED SOLUTION
BA_Pete
Super User
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}}),
    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

View solution in original post

1 REPLY 1
BA_Pete
Super User
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}}),
    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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.