power squared: powerquery with powerbi€¦ · • use functions to call apis for data, such as...

Power Squared: PowerQuery with PowerBI

Solution Architect – Business Intelligence and Advanced AnalyticsEmail: bcampbell@hitachisolutions.com

Bryan Campbell

#HSCCATLANTA19

Session Track: Analytics and AI

Power Query Introduction

Folder As A Source

SQL Query Mashup

Scrape the Web with Functions

01

02

03

04

05

C O N T E N T S

/ 4Think Digital Customer Conference 2019

Power Query – The little ETL engine that could…

• Based on the M query language• No or Low coding required• Customizable• Can leverage on prem and cloud sources• Exists in Excel 2013 and beyond

A brief introduction

/ 5Think Digital Customer Conference 2019

3 Use Cases

• Folder as a Source• Any business analyst can build an ETL process• Excel has the same engine behind the scenes

• SQL Query Mashup – Query Folding• “View Native Query”• Let Power BI write your SQL and optimize your data import

• Scraping Web Pages via Functions• Use of parameters to create functions• Automate webscrapes, or other repeatable tasks

/ 6Think Digital Customer Conference 2019

Folder as a Source

• Analysts will often run repetitious tasks over and over to extract source data

• Typical query interfaces will have timeouts built in

• Parameters are sometimes applied to “break up” the work into smaller chunks

• These chunks are then put back together with VBA code, macros, Excel ‘magic’

/ 7Think Digital Customer Conference 2019

Folder as a Source

• About this dataset:• Broken into 5 pieces• Pivoted by the source• Contains double row header, but no footer or merged cells

• Demo

/ 8Think Digital Customer Conference 2019

Folder as a Source

• Attach to the folder of like files• Remove any ‘not like’ files

• Train PQ to handle the structure of the data• Headers, footers, subtotals, errors in the data

• Unpivot• Often times data is pre-summarized by the source

• Create a repeatable process• And this exists in Excel???

Summary

/ 9Think Digital Customer Conference 2019

SQL Query Mashup – Query Folding

• Power Query contains a GUI based SQL writer• “View Native Query”

• Proper selection of steps early in the transform will help optimize server load• Merge = Join• Append = Union

/ 10Think Digital Customer Conference 2019

SQL Query Mashup – Query Folding

• Speed of refresh improvements• Pulling fewer columns and/or

fewer rows means less server load and network load

/ 11Think Digital Customer Conference 2019

SQL Query Mashup – Query Folding

• Size of data model

/ 12Think Digital Customer Conference 2019

SQL Query Mashup – Query Folding

• Aggregation of the data at the source…

/ 13Think Digital Customer Conference 2019

SQL Query Mashup – Query Folding

• Power Query contains a GUI based SQL writer• “View Native Query”

• Proper selection of steps early in the transform will help optimize server load• Merge = Join• Append = Union• Custom SQL is NOT optimized• Queries on Views are less efficient, but still improve

Summary

/ 14Think Digital Customer Conference 2019

Web Page as a Source - Parameters and Functions

• www.boxofficemojo.com dataset• All time domestic gross

/ 15Think Digital Customer Conference 2019

Web Page as a Source - Parameters and Functions

• Dataset is paginated over several pages• We want to pull this data• But not 1 page at a time…

• Demo

/ 16Think Digital Customer Conference 2019

Web Page as a Source - Parameters and Functions

• www.boxofficemojo.com dataset• Go to AllTime, then DOMESTIC

• Bring in a page via the webpage as a source, name this query WebPage• Set up a Parameter as TEXT, call this parameter PAGENUM• Insert this Parameter into the query formula bar• Create a blank query “DATA” = {1..3}, covert to table and datatype = text• Create the function fxGetWebPage from the WebPage query• Invoke custom function to your generated list query, and expand• Disable loading of the fxGetWebPage• Advanced Editor – add in a “WAIT UNTIL”• Check datatypes and errors

Step by Step Review

/ 17Think Digital Customer Conference 2019

Comments

• Power Query is often overlooked• A stepping stone for business analyst to grow further, from the Excel skillset

into relational database skillset• Query Mashup is UNDERUTILIZED – too important to not take advantage of• Use functions to call APIs for data, such as Microsoft Cognitive API for

language translation, key phrase extraction, sentiment analysis, image tagging, or machine learning models

• Don’t forget this entire engine is both behind Excel and in the Web (Dataflows, Microsoft Flow, PowerApps, etc)

/ 18Think Digital Customer Conference 2019

Power Squared: PowerQuery with PowerBI

Questions??

• Bryan Campbell• bcampbell@hitatchisolutions.com