Power Query Tips and tricks Erik Svensen Data

Power Query – Tips and tricks Erik Svensen #Data. BISummit

Me am I • • • CTO @ Cat. Man Solution A/S – supplier of BIAAS Customers within the FMCG and Retail industry Work with Excel, Power BI, SSAS, SSRS, MDX, DAX and M Have been working Power BI since it beginning in the good old Microsoft Query and VBA days Speaker at SQL Saturdays in CPH and Oslo + SQL Nexus PUG Leader – Denmark Power BI User Group Reach me at Twitter: @donsvensen Blog: http: //eriksvensen. wordpress. com Mail: es@catmansolution. com #Data. BISummit

Version of Power BI Desktop #Data. BISummit

Why should you learn more about M • Not all possibilities is visible through the interface • • • You can make your queries perform better You can make them smarter than the “recorder” You can make them dynamic You can make them error-proof Make them work in the future as well And you can use it in Excel #Data. BISummit

Another reason to learn M • Analysis Services 2017 gives you Power Query when creating tables in your tabular models • The common data model for analytic will give you Power Query as well And wouldn’t be great to see it in SSRS or SSIS ? #Data. BISummit

Your friends when working in the advanced editor • =#shared • Show formula bar • Add Advanced editor to Quick Access toolbar #Data. BISummit

And last but not least #Data. BISummit

Demo #Data. BISummit

Datasets • Adventure Works • Collisions in New York • Handle the different sheet names • Dynamic number of columns – Excel files • Dynamic column names in files #Data. BISummit

And More can be done • Loops • If • Nested functions • Error handling • Try otherwise • And more #Data. BISummit

Take aways - The interface can do a lot but you can do even more using the AE - Remember the interface works with absolute references and you properly don’t want that - It is actually very fun to make it work - Pray every day for intellisense in the AE to arrive soon - Learn M #Data. BISummit

Resources Formula reference https: //msdn. microsoft. com/en-us/library/mt 211003. aspx Formula Language Specification-Syntaks Link Chris Webb https: //blog. crossjoin. co. uk/ Community https: //community. powerbi. com/ Pitfalls blog series http: //datachant. com/2017/01/06/10 -mistakes-you-always-do-inpowerbi-powerquery/ #Data. BISummit

Questions • ? • Twitter - @donsvensen • Mail – es@catmansolution. com #Data. BISummit

Thank you and please evaluate #Data. BISummit