Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |