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

[chloebelle]

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.

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 Stop	Last Stop	# of stops
7:07:00 AM	1:08:00 PM	6
2:45:00 AM	8:25:00 AM	7
5:49:00 AM	2:01:00 PM	18
5:20:00 AM	12:25:00 PM	14
6:20:00 AM	7:00:00 AM	3
5:06:00 AM	10:12:00 AM	14
8:20:00 AM	12:10:00 PM	9
5:05:00 AM	12:37:00 PM	14
3:50:00 AM	1:45:00 PM	7
9:38:00 AM	11:42:00 AM	5
6:45:00 AM	1:00:00 PM	9
7:30:00 AM	3:15:00 PM	10
4:50:00 AM	12:38:00 PM	14
5:45:00 AM	11:20:00 AM	13
3:05:00 AM	9:20:00 AM	9

[bolfri · Accepted solution]

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:

[chloebelle]

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?

[bolfri]

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. 😉 

[chloebelle]

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]

New Source > Blank Query

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

[chloebelle]

Thank you!!!

[chloebelle]

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]

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.

[chloebelle]

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]

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

[chloebelle]

Still not working...

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.

[Syk]

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
)

[chloebelle]

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

[AmiraBedh]

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

[chloebelle]

First StopLast Stop# of stops

7:07:00 AM	1:08:00 PM	6
2:45:00 AM	8:25:00 AM	7
5:49:00 AM	2:01:00 PM	18
5:20:00 AM	12:25:00 PM	14
6:20:00 AM	7:00:00 AM	3
5:06:00 AM	10:12:00 AM	14
8:20:00 AM	12:10:00 PM	9
5:05:00 AM	12:37:00 PM	14
3:50:00 AM	1:45:00 PM	7
9:38:00 AM	11:42:00 AM	5
6:45:00 AM	1:00:00 PM	9
7:30:00 AM	3:15:00 PM	10
4:50:00 AM	12:38:00 PM	14
5:45:00 AM	11:20:00 AM	13
3:05:00 AM	9:20:00 AM	9

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

[Syk]

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
)

[chloebelle]

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!