Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- Excel formulas in Power Query

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Excel formulas in Power Query

10-04-2021
05:00 AM

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.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2021
08:31 AM

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

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2021
08:31 AM

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