Interactive Excel Dashboards with Power Pivot & Power Query



Power Query & Pivot Pivot



• Connect to tons of data sources
• One click refresh
• Power Query & Power Pivot skills tranferable to Power BI
• Applies to Excel 2010 onward for Windows only
• Using Excel 2016 Office 365
Power Query and Power Pivot available in Excel 2010 onward (Excel file contains specific version information)
Map Charts only available in Excel 2016 Office 365

• Automatic Dates Grouping (year, month) in Excel 2016
• One pivot table per sheet. Give each Pivot Table a name
• Filter in Power Pivot
◇ Top n
• Power Pivot Measure
◇ Custom formula builder
◇ DIVIDE(SUM(<field>)/SUM(<field>), “”) handles division by 0 error
• Map Charts
◇ Can't directly create Map Chart from data
◇ Copy pivot table data and paste to other columns
◇ Create Map Chart using other columns
◇ Change axis data range to original Pivot table afterwards
◇ Use Legend as Chart Title
▪ Chart Element > Legend > Top
• Sparkle Line
◇ Use relative dynamic named range
◇ OFFSET()
• Beautify
◇ Hide filter button on pivot chart
▪ Right click > Hide Axis Field Button on Chart
▪ Right click > Hide All Field Button on Chart
◇ Hide Gridline, Axis and Lengend
◇ Copy format from one chart to another chart (ctrl-c ctrl-v??). The charts must be on the same chart type
▪ Remove border: Format > Shape Outline > None
▪ Press F4 to repeat formatting
◇ Hide gridlines, columns, scroll bars
• Slicers
◇ Insert > Slicers
◇ Sizing: Options > Buttons, Options > Size
◇ Alignment: Options > Align
◇ Link to all charts: Right click > Report Connections
• Differentiate different things in colors
◇ Sales related, Shipping Date related etc.
• Future Updates
◇ Add files to the folder Power Query is referecing
◇ Data > Refresh All

(Explorer) Icon “Copy Path”
Data > Get Data > From File > From Folder
Combine and Edit

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Source



https://www.youtube.com/watch?v=TK0CJBaqvnY

Index