Use the dynamic expression editor for Dataflow Gen2 data destinations (preview)

The dynamic expression editor helps you build values in supported data destination fields by combining text with values that resolve when the dataflow runs. For example, you can include the UTC refresh date in a file name, use a parameter for a table name, or include a workspace-specific value.

The editor is enabled by default. When you use it for the first time, a teaching callout explains how to open the dynamic value menu.

Available values

The values available in the editor depend on the dataflow and workspace configuration.

Create a dynamic table or file name

1. In the Power Query editor, add a data destination to a query.

2. Place the cursor in a destination field that supports dynamic expressions, such as Table name or File name.

3. Enter any text that should remain the same each time the dataflow runs.

4. Press / to open the dynamic value menu.

   

5. Select a dynamic value, parameter, or workspace variable. You can also start typing after / to filter the menu.

6. Continue entering text and values until the expression is complete. For a file destination, include the file extension as text.

   

7. Finish configuring the data destination, and then publish the dataflow.

At run time, Dataflow Gen2 resolves each token and combines the values with the text in the expression. Normal destination behavior still applies to the resulting name. For example, a file destination can overwrite a file when the resolved file name matches an existing file.

Edit an expression

You can edit text and insert more values anywhere in the expression.

• To open the menu without using the mouse, press /. Use the arrow keys to move through the menu, and then press Enter to insert the selected value.
• To close the menu without selecting a value, press Esc. The / and any text you entered after it remain in the field as text.
• To remove an inserted value, place the cursor next to its token and press Backspace or Delete.

Example expressions

In the editor, the values shown in angle brackets in these examples appear as tokens. You don't type the angle brackets.

Turn the dynamic expression editor off or on

The dynamic expression editor is enabled by default. Your preference is stored in a browser cookie and isn't saved in the Fabric service. As a result, the setting doesn't carry across browsers or devices and can return to its default if you clear your browser cookies. To change it:

1. In the Power Query editor, select Options from the Home tab.
2. In the Options dialog, select Global > General.
3. Scroll to the Data destinations section.
4. Under Editor, clear Enable dynamic expression editor to turn the editor off. Select the checkbox to turn it back on.
5. Select OK.

Turning the editor off doesn't remove dynamic expressions that are already configured.

Considerations and limitations

• Availability depends on the selected data destination and field.
• The editor combines text and available values. It isn't a general-purpose Power Query M expression editor.
• A referenced parameter or workspace variable must be available when the dataflow runs.
• Changing a parameter, workspace variable, date, or time value can cause the resolved destination name to change between runs.
• To parameterize destination settings that aren't supported by this editor, see Advanced edit for data destination queries.

Related content

• Dataflow Gen2 data destinations and managed settings
• Create parameters for dataflows
• Use Fabric variable libraries in Dataflow Gen2
• Data destinations validation rules