Pivot Tables & Power Query
Get Pivot Data
Pull values from pivot table
Extract Slicer Selection to Cell
Add this VBA first (no edit needed)
Public Function GetSelectedSlicerItems(SlicerName As String) As String Dim oSc As SlicerCache Dim oSi As SlicerItem Dim lCt As Long On Error Resume Next Application.Volatile Set oSc = ThisWorkbook.SlicerCaches(SlicerName) If Not oSc Is Nothing Then For Each oSi In oSc.SlicerItems If oSi.Selected Then GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", " lCt = lCt + 1 ElseIf oSi.HasData = False Then lCt = lCt + 1 End If Next If Len(GetSelectedSlicerItems) > 0 Then If lCt = oSc.SlicerItems.Count Then GetSelectedSlicerItems = "All Items" Else GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) End If Else GetSelectedSlicerItems = "No items selected" End If Else GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found" End If End Function
In the cell you want to show value
Slicer name can be found by right-click slicer and select slicer settings
Name to use in formulas line
Called Get & Transform in Excel 2016.
|Microsoft Power Query for Excel||Link to Download|
Filter Query using Cell
- 1. Type a filter value into blank cell
- 2. Keep the cell selected
- Select the Data tab / From Table/Range (uncheck my table has headers)
- 3. Query Editor will open
- Right-click the cell with your filter value then "Drill Down"
- Change the name of your query to anything e.g., jmfilter (Query Settings are on the right panel or choose View / Query Settings)
- 4. (Inside Query Editor) Connect your data source or select the query with the data you want to filter
- Manually filter the column to one selection e.g., Texas (select the arrow next to the column and uncheck everything but one filter option)
- The formula bar should show the filter you just created
- Change the formula where it says your filter value e.g., Texas to say jmfilter with no quotes
= Table.SelectRows(Source, each ([Location] = "Texas")) Change to: = Table.SelectRows(Source, each ([Location] = jmfilter))
Merging Two Tables
The two tables will be joined based on a column with a matching record.
- Create a query for both tables
- Click on Table
- Power Query (in Ribbon) / From Table
- On right side (query settings) change name to tablename_import
- Home Ribbon / Close and Load to... / Only Create Connection
- Merge the Tables
- Power Query (in Ribbon) / Merge
- The first (top) table is your Primary table and will have all the rows (unless elected to show match only).
- The second (bottom) table will be added as a column(s) to the first table, only if they have a matching column from primary (match-only button doesn't matter).
- Expand a Column
- In the Preview grid, click the NewColumn expand icon
- In the Expand drop-down:
- Click (Select All Columns) to clear all columns.
- Click the columns from the related table to add to the primary table.
- Click OK.
Merging two rows
Merge first two rows to create a table header
- 1. Transpose table
- 2. Merge two first columns with delimiter
- 3. Transpose again (reverse)
- 4. Promote headers
Power Query will sometimes automatically "Change Type" of one of your columns. Always review the Applied Steps under the source item.
Formula's are case sensitive
If (AND) Statements:
if [Level] = "Executive" and [Target Met] = "Yes" then [Salary] * .05 else 0
If (OR) Statements:
if [Delivery Date] <> null or [Order Type] = "In Store Pickup" then "Completed" else null
Nested If Statements
if [Level] = "Executive" and [Target Met] = "Yes" then [Salary]*.05 else if [Level] = "Manager" and [Target Met] = "Yes" then [Salary]*.10 else 0
Text.Contains("abc", "a") returns true or false