Creating formulas with AI
In addition to helping you build queries, Omni's AI can also help you write complex, Excel-like formulas using natural language. Having AI create calculated fields for you reduces the time you'll have otherwise spent searching for the right syntax and iterating to get it just right.
Then, when you're happy with the results, you can push the field to the shared model to make it reusable!
Requirements
To use AI formulas, the Workbook calculations AI setting in your Organization settings must be enabled.
Note: This feature is enabled by default.
Supported functions & formulas
The AI formula builder can use any of the formulas supported in manually-created calculated fields. Refer to the Functions & formulas reference for a complete list of supported functions.
Creating AI formulas
AI formulas can only be created using the steps outlined in this section. The query helper doesn't currently support creating formula fields.
-
In a workbook, click the ✨ (three stars) in the calculations bar:
-
In the text box, describe the formula you'd like to create. You can include the function to use if you know it, but otherwise, it's helpful to be specific about what you're trying to achieve. Refer to the Examples section to get some inspiration.
If you're not sure where to begin, click Get suggestions in the formula prompt window to generate a list of potential starting points:
-
When finished, click -> to submit the prompt to AI.
Pushing AI formulas to the shared model
To reuse an AI-generated calculated field, you'll need to push it to the shared model. You can do this by:
- Saving the query as a query view (Model > Save as query view)
- Promoting the workbook changes.
Examples
Two letter abbreviations for US states
Prompt
Using IFS, create a formula that uses the standard two letter abbreviations for US states. For District of Columbia, use DC as the abbreviation.
Generated formula
In the example query, the A column contains the full name of the state. For example, California or Pennsylvania
=IFS(A1 = "Alabama", "AL", A1 = "Alaska", "AK", A1 = "Arizona", "AZ", A1 = "Arkansas", "AR", A1 = "California", "CA", A1 = "Colorado", "CO", A1 = "Connecticut", "CT", A1 = "Delaware", "DE", A1 = "Florida", "FL", A1 = "Georgia", "GA", A1 = "Hawaii", "HI", A1 = "Idaho", "ID", A1 = "Illinois", "IL", A1 = "Indiana", "IN", A1 = "Iowa", "IA", A1 = "Kansas", "KS", A1 = "Kentucky", "KY", A1 = "Louisiana", "LA", A1 = "Maine", "ME", A1 = "Maryland", "MD", A1 = "Massachusetts", "MA", A1 = "Michigan", "MI", A1 = "Minnesota", "MN", A1 = "Mississippi", "MS", A1 = "Missouri", "MO", A1 = "Montana", "MT", A1 = "Nebraska", "NE", A1 = "Nevada", "NV", A1 = "New Hampshire", "NH", A1 = "New Jersey", "NJ", A1 = "New Mexico", "NM", A1 = "New York", "NY", A1 = "North Carolina", "NC", A1 = "North Dakota", "ND", A1 = "Ohio", "OH", A1 = "Oklahoma", "OK", A1 = "Oregon", "OR", A1 = "Pennsylvania", "PA", A1 = "Rhode Island", "RI", A1 = "South Carolina", "SC", A1 = "South Dakota", "SD", A1 = "Tennessee", "TN", A1 = "Texas", "TX", A1 = "Utah", "UT", A1 = "Vermont", "VT", A1 = "Virginia", "VA", A1 = "Washington", "WA", A1 = "West Virginia", "WV", A1 = "Wisconsin", "WI", A1 = "Wyoming", "WY", A1 = "District of Columbia", "DC")
Results table
| State | State Abbreviation |
|---|---|
| Alabama | AL |
| Alaska | AK |
| Arizona | AZ |
| Arkansas | AR |
| ... | ... |
Extract domain from email
Prompt
Give me the domain from the email column
Generated formula
In the example query, the A column contains an email address. For example, blobby@blobsrus.com
=RIGHT(A1, LEN(A1) - FIND("@", A1))
Results table
| Email Domain | |
|---|---|
blobby@blobsrus.com | blobsrus.com |
blobross@gmail.com | gmail.com |
bobbyparton@blobbyworld.co | blobbyworld.co |
Identify a date as a weekday or a weekend
Prompt
Tell me if COLUMN_A is a weekday or a weekend.
If you receive an error for this prompt, try this instead:
Tell me if COLUMN_A is a weekday or a weekend. If using WEEKDAY, only provide a date as an argument.
Generated formula
In the example query, the A column contains a timestamp.
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),"Weekend","Weekday")
Results table
| Date | Weekday or Weekend |
|---|---|
| 2024-12-27 00:25:19.000 | Weekday |
| 2024-09-30 13:11:47.000 | Weekend |
| 2023-04-27 18:59:25.000 | Weekday |
| 2022-05-15 18:02:33.000 | Weekday |