Transformations that can save your ETL in Power

Transformations that can save your ETL in Power BI Ana María Bisbé York @ambynet anabisbe@amby. net BI Consultant. Microsoft Partner PUG – Spain @pbiusergroup #Data. BISummit

Objectives • Definition of ETL process • ETL process in Power BI • Power BI ETL • Extraction • Transform • Data quality • Data transformation • Load #Data. BISummit

ETL process • A layer of a Business Intelligence system • “Hidden layer“ • As hard to implement as necessary for the success of a BI project #Data. BISummit

ETL process in Power BI Extract • Files • Data Bases • Folders • Scripts • Cloud Transform • Data Quality • Filter Rows • Filter Columns • Format as Table • Combine queries • Pivot / Unpivot Load • Enable load • Relationship • Hide Columns #Data. BISummit

Power BI ETL - Extract • Connect to a large amount of data sources • Connection and navigation wizards • Import data vs live connection #Data. BISummit

Power BI ETL - Transform • Table • Transpose vs Reverse • Any Column • Pivot vs Unpivot • Fill • Replace values #Data. BISummit

Power BI ETL - Transform • Filters by rows or columns • Data type of column • Rows control #Data. BISummit

Power BI ETL - Transform • Problem: Different data types in columns • Solution: Pivot column #Data. BISummit

Power BI ETL - Transform • Problem: Different data types in columns • Solution steps: • • • Fill up Filter rows Remove columns Transpose Unpivot #Data. BISummit

Power BI ETL - Transform • Data cleansing • Problem: Incorrect conversion of the type of data • Solution: Convert using locale #Data. BISummit

Power BI ETL - Transform • Data cleansing • Get data • Split • Group • Problem: Values are not grouped correctly • Solution: Cleansing before grouping #Data. BISummit

Power BI ETL - Load • Enable load to the tabular model • Check relationships • Improve tabular model #Data. BISummit

Demo #Data. BISummit

Extract from • Files. rpt • Folder • Clipboards • R script • Excel table • SQL Server tables #Data. BISummit

Transform into • Data model • Additional queries • Parameters and functions #Data. BISummit

Load to • Tabular model #Data. BISummit

Conclusions • Query editor in Power BI is a powerful tool for ETL purposes • Data could be: • Extracted from many different sources • Transformed from different format to model • Cleansed • Improved taking into account business requirements • Loaded into a Tabular model #Data. BISummit

Resources • Books • M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query • Power Query for Power BI and Excel #Data. BISummit

Resources • Videos Power BI • Microsoft Power BI (youtube) • Magic Mondays from Curbal (Ruth Pozuelo) #Data. BISummit

Resources • Blogs • • • Power Pivot Pro Chris Webb’s BI Blog Power Query Training Reza Rad, Leila Etaati & Philip Seamark Data and Analytics with Dustin Ryan Ken Puls Data Analysis with David Eldersveld Imke Feldmann Teo Lachev – Prologika Kasper On BI Matt Allington Data and Analytics with Dustin Ryan #Data. BISummit

Questions? #Data. BISummit

Thank you ! #Happy. Quering Ana María Bisbé York @ambynet anabisbe@amby. net BI Consultant. Microsoft Partner PUG – Spain @pbiusergroup #Data. BISummit

© 2018 Dynamic Communities. All rights reserved.