Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!