Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chloebelle
Regular Visitor

How to enter equation (originally in Excel) into Power BI?

I'm trying to learn Power BI to automate KPIs and sales reports, and doing so means that I would have my equations automated as well. I'm really struggling with figuring out how to make this work - I added a new column and have been trying to input my equation. It keeps giving me an error: "The following syntax error occurred during parsing: Invalid token, Line 1, Offset 13, #."

 

Here's the error and the equation I'm trying to use.

chloebelle_1-1690919731008.png

Basically, ((Last Stop - First Stop) * 24) / (# of stops * 60). This equation works completely fine in Excel, just not Power BI. Here's some sample data (which I have converted to 24h time in Power BI):

 


First StopLast Stop# of stops
7:07:00 AM1:08:00 PM6
2:45:00 AM8:25:00 AM7
5:49:00 AM2:01:00 PM18
5:20:00 AM12:25:00 PM14
6:20:00 AM7:00:00 AM3
5:06:00 AM10:12:00 AM14
8:20:00 AM12:10:00 PM9
5:05:00 AM12:37:00 PM14
3:50:00 AM1:45:00 PM7
9:38:00 AM11:42:00 AM5
6:45:00 AM1:00:00 PM9
7:30:00 AM3:15:00 PM10
4:50:00 AM12:38:00 PM14
5:45:00 AM11:20:00 AM13
3:05:00 AM9:20:00 AM9
1 ACCEPTED SOLUTION
bolfri
Super User
Super User

It will be easier for you to do this in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVA5DsQgDPzKijqFbTCHu31ApPQo///GAvGCiUQzeDyHa3VJoD34fE93OBTIHVwdRHcf1ZEEnvMstEAac5ZQ5hcJ4NzHrASCZUCq8DDCYETL6GEm8KoAcSmANJGJHoX88kCYHuUvwZbg0zuEFzYSWvpaPYv4vOaNsEKwtjCHQm1hIiTxppigOQMMQtgSkPrZjLxZ4FbaawvTs1hCC3H/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Stop" = _t, #"Last Stop" = _t, #"# of stops" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Stop", type time}, {"Last Stop", type time}, {"# of stops", Int64.Type}}),
    #"Add Duration" = Table.AddColumn(#"Changed Type", "Duration", each [Last Stop]-[First Stop]),
    #"Add Duration per stop" = Table.AddColumn(#"Add Duration", "Duration per stop", each [Duration]/[#"# of stops"]),
    #"Add duration per stop in minutes" = Table.AddColumn(#"Add Duration per stop", "Duration per stop in minutes", each Duration.TotalMinutes([Duration per stop]))
in
    #"Add duration per stop in minutes"

Results:

bolfri_0-1690921575172.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

17 REPLIES 17
bolfri
Super User
Super User

It will be easier for you to do this in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVA5DsQgDPzKijqFbTCHu31ApPQo///GAvGCiUQzeDyHa3VJoD34fE93OBTIHVwdRHcf1ZEEnvMstEAac5ZQ5hcJ4NzHrASCZUCq8DDCYETL6GEm8KoAcSmANJGJHoX88kCYHuUvwZbg0zuEFzYSWvpaPYv4vOaNsEKwtjCHQm1hIiTxppigOQMMQtgSkPrZjLxZ4FbaawvTs1hCC3H/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Stop" = _t, #"Last Stop" = _t, #"# of stops" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Stop", type time}, {"Last Stop", type time}, {"# of stops", Int64.Type}}),
    #"Add Duration" = Table.AddColumn(#"Changed Type", "Duration", each [Last Stop]-[First Stop]),
    #"Add Duration per stop" = Table.AddColumn(#"Add Duration", "Duration per stop", each [Duration]/[#"# of stops"]),
    #"Add duration per stop in minutes" = Table.AddColumn(#"Add Duration per stop", "Duration per stop in minutes", each Duration.TotalMinutes([Duration per stop]))
in
    #"Add duration per stop in minutes"

Results:

bolfri_0-1690921575172.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




If you don't mind me asking (otherwise just ignore this): I need to apply the equation to an entire dataset which will continually be updated. How can I make the code source from that table rather than from a static set of numbers?

Change the "Source" step to your dataset. You can create your dataset and then go to Advanced Editor, Copy & Pase part of your code into mine or take part of mine code and pase it into your code. 😉 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sorry, could you ELI5 how to get to the spot in Power Query Editor where I can input all that? Is it R/Python script?

bolfri_0-1690923101602.png

New Source > Blank Query

Then go to Advanced Editor > Copy & Paste a code from me

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you!!!

I'm pretty new to all this so it'll take me a few to mull that over, but your answers match my historical data! Thank you!

AmiraBedh
Resident Rockstar
Resident Rockstar

In Power BI, you're dealing with the DAX (Data Analysis Expressions) language, which is somewhat different from the formulas in Excel. It provides functionality for handling tables and columns of data, not individual cells like Excel.

Let's break down your equation to Power BI format.

If your columns are named `First Stop`, `Last Stop` and `# of stops`, then your DAX formula might look something like this:

First, you will want to convert your time to numbers so you can perform mathematical operations on them. For instance, you can convert the time to total hours. You can achieve this with the HOUR function which extracts the hour as a number from a time value.

Next, you can perform the subtraction and multiplication by 24.

Finally, you can divide by the multiplication of `# of stops` and 60.

Putting all these together, you should have something like this:

```
New_Column = ((HOUR([Last Stop]) - HOUR([First Stop])) * 24) / ([# of stops] * 60)
```

However, the `HOUR` function only gives the hour part of a time. If you want to be more precise to include minutes, you should convert the time to decimal hours. You can achieve this by extracting the hour and minute parts, then adding them together after converting minutes to a fraction of an hour:

```
New_Column = (((HOUR([Last Stop]) + MINUTE([Last Stop])/60) - (HOUR([First Stop]) + MINUTE([First Stop])/60)) * 24) / ([# of stops] * 60)
```

This should give you the result you're after, as it properly handles the times and performs the necessary calculations.

Please replace `New_Column`, `First Stop`, `Last Stop`, `# of stops` with your actual column names if they are different. Also, be aware of DAX's case sensitivity when writing formulas.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Ooh, the time was giving me some issues earlier so this is super helpful to know! Similar to my reply below, however, this seems to only work with the first part of the equation. Once I try to divide everything by the # of stops, it gives me an error saying "The result of a conversion or arithmetic operation is either too large or too small." Not sure why.

 

Thank you for the thoughtful response!

Syk
Super User
Super User

If this is a column, just delete all the # and it should work for you.

Still not working...

chloebelle_0-1690920701109.png

I tried to just do the first part of the equation and that worked, but it won't let me divide without giving the above error.

Try this

Time per stop =
DIVIDE (
    DATEDIFF (
        'Driver Survey'[Last Stop],
        'Driver Survey'[First Stop],
        HOUR /*or whatever time you need, you can use minute, day,or seconds here as well*/
    ) * 24,
    [# of stops] * 60,
    0
)

That didn't give me an error but all the numbers are negative for some reason, that seems to be getting us somewhere though!

Can you provide a sample of your PBI file and your code as text instead of images ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

First StopLast Stop# of stops
7:07:00 AM1:08:00 PM6
2:45:00 AM8:25:00 AM7
5:49:00 AM2:01:00 PM18
5:20:00 AM12:25:00 PM14
6:20:00 AM7:00:00 AM3
5:06:00 AM10:12:00 AM14
8:20:00 AM12:10:00 PM9
5:05:00 AM12:37:00 PM14
3:50:00 AM1:45:00 PM7
9:38:00 AM11:42:00 AM5
6:45:00 AM1:00:00 PM9
7:30:00 AM3:15:00 PM10
4:50:00 AM12:38:00 PM14
5:45:00 AM11:20:00 AM13
3:05:00 AM9:20:00 AM9

 

Here's a table version of the sample data, hope that helps

Should give you what you need!

Time per stop = 
DIVIDE (
    DATEDIFF (
        'Driver Survey'[First Stop],
        'Driver Survey'[Last Stop],
        HOUR /*or whatever time you need, you can use minute, day,or seconds here as well*/
    ) * 24,
    [# of stops] * 60,
    0
)

Oh yup, it's a positive number now haha. The solution is different than what I have in my historical data but this gives me a great starting point, thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors