Pivot Tables & Power Query

From Wiki
Jump to: navigation, search

Pivot Tables

Get Pivot Data

Pull values from pivot table
http://www.contextures.com/xlPivot06.html#GetPivotData

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
=GetSelectedSlicerItems("Slicer_name")

Slicer name can be found by right-click slicer and select slicer settings
Name to use in formulas line

http://www.jkp-ads.com/Articles/slicers05.asp

Power Query

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).
Merging two tables
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.
Expanding Column



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

Random Tips

Power Query will sometimes automatically "Change Type" of one of your columns. Always review the Applied Steps under the source item.

Formulas

Reference Manual
http://download.microsoft.com/download/8/1/A/81A62C9B-04D5-4B6D-B162-D28E4D848552/Power%20Query%20Formula%20Language%20Specification%20(October%202016).pdf

Functions List
https://msdn.microsoft.com/en-us/library/mt779182.aspx

IF Statements

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

Other

Text.Contains("abc", "a")
  returns true or false