How to Create a Power Query Variable from Either an Excel Cell or a Value Inside A Power Query Table

This video quickly shows the steps to take a cell inside excel and turn it into a variable that can be used inside Power Query and adjusted as needed inside the excel sheet. It walks through the steps of turning the cell into a table, moving that table inside Power Query, and then using Record.Field() line item from M Code to create the variable.

Steps:
0:00 Initial Explanation and Code Structure
1:41 Beginning of Example - Initial Explanation
3:06 Steps For Turning the Cell Into A Table
3:43 Moving Table Into Power Query
4:00 Using Record.Field Line
4:25 Writing the Variable Into the Power Query Table.
4:50 Credit
5:05 Keep The Header Row On For Single Cell Tables

[rodolfoflores]

Thanks for this tutorial. It works like a charm

[alecj]

Thanks Andrew - this is EXACTLY what I needed to make a cell that could be used to filter out immaterial transactions!

[AndrewMagee]

I realized after finishing this that I didn't go over the step to get the table to update automatically when updating the cell. I talk about this at the 19m 47s mark of the "How to Switch Through Data Tables with Single Dropdown Box in Microsoft Excel" video. You open VBA and insert the following code into the module:

Sub Refresh()
- the name of the table").Refresh
End Sub

(The name of the table is whatever you named the query inside power query.)

And then in the object sheet of the sheet that has the cell on it, you'll enter the following code:

Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Address = $A$1 _
Then Call Refresh
Else
End If
End Sub

(the target address of A1 should be updated to the location of whatever cell you are wanting to use as the variable.)