Variable.Value

What is Variable.Value?

Variable.Value is an M function that returns the value of the specified variable. It is often used to store intermediate values that are needed for further calculations. Using Variable.Value helps to avoid duplicating computations and improve the performance of your queries.

For example, suppose you have a table that contains sales data for different products. You want to calculate the total sales for each product. Instead of calculating the total for each product separately, you can use Variable.Value to store the intermediate result and use it to calculate the grand total.

Syntax of Variable.Value

The syntax of Variable.Value is straightforward. Here is how it looks like:

where variableName is the name of the variable whose value you want to retrieve.

How to use Variable.Value in Power Query

To use Variable.Value in Power Query, you need to create a new variable and assign a value to it. You can then use Variable.Value to retrieve the value of the variable and use it in further calculations.

Here is an example of how to use Variable.Value in Power Query to calculate the total sales for each product:

1. Open Power Query and load the sales data into a new query.

2. Create a new variable by clicking on the ‘New Source’ button on the ‘Home’ tab and selecting ‘Blank Query’.

3. In the ‘Query Editor’, click on ‘View’ and select ‘Advanced Editor’.

4. In the ‘Advanced Editor’, enter the following M code:

In the code above, we create a new variable called ‘SalesReportTotal’ and assign the value of the ‘Grand Total’ row to it using the Variable.Value function.

5. Click ‘Done’ to save the changes and return to the ‘Query Editor’.

6. Click on the ‘New Source’ button on the ‘Home’ tab again and select ‘Blank Query’.

7. In the ‘Query Editor’, click on ‘View’ and select ‘Advanced Editor’.

8. In the ‘Advanced Editor’, enter the following M code:

In the code above, we create a new table called ‘SalesReport’ that contains the sales data and the ‘Grand Total’ row.

9. Click ‘Done’ to save the changes and return to the ‘Query Editor’.

10. Close the ‘Query Editor’ and load the data into a data model.

Now, you can use the ‘SalesReportTotal’ variable in further calculations to get the total sales for each product.

Variable.Value is a powerful M function in Power Query that helps to store intermediate values and improve the performance of your queries. By using Variable.Value, you can avoid duplicating computations and make your queries more efficient. With the syntax and examples provided in this article, you should have a good understanding of how to use Variable.Value in your Power Query projects.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us