Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!