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: [email protected]

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


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


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’


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


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


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



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

fewer rows means less server load and network load



• Size of data model



• Aggregation of the data at the source…



• 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


Web Page as a Source - Parameters and Functions

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

http://www.boxofficemojo.com/



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

• Demo



• 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

http://www.boxofficemojo.com/


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)


Power Squared: PowerQuery with PowerBI

Questions??

• Bryan Campbell• [email protected]

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

Documents