1 / 16

Power query and old school

Learn all about Power Query, M Language, and data harvesting in XML format with insights from Julie Smith and Rob Volk, SQL Server experts. Discover tools, functions, and resources for powerful data analysis in Excel.

annick
Télécharger la présentation

Power query and old school

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Power query and old school Harvesting xml data

  2. Julie smith • Data Services Consultant at Innovative Architects in Atlanta, GA • One half of the DatachiX.com with Audrey Hammonds • SQL Server mvp • @juliechix • Always forget to upload my materials • Not today! http://sdrv.ms/LtBcxF

  3. rob volk • Rob Volk is a database administrator from the Atlanta, Georgia area • Blogs at weblogs.sqlteam.com/robv • @sql_r • Former mvp

  4. Today’s agenda • Story of how annoyed rob was with planning sql Saturdays • Has to be a better way • Foraging for data—on speakers, venues, strange coincidences • Power query –first came out as data explorer • Curl • Querying XML with SQL (transact sql to be exact)

  5. IntroducingPower Query • Released july 2013 • Excel add-in for 2010 or 2013 • Self service etl!!

  6. Power bi suite • Powerpivot – in memory embedded data models • Powermap • Power view – presentation ready visualizations • Power query • All in excel !!!!

  7. Power bi • Office 365 • Data management gateway • Collaborate with office 365 sharepoint • http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/power-bi-admin-center-help-HA104078330.aspx

  8. History • M Language for business analysts • Project oslo • Domain specific language • M has morphed into the power query for excel formula language • Download a pdf here: http://go.microsoft.com/fwlink/?LinkID=320633

  9. Julie When She First Met power query…

  10. It can harvest the following: • “other” • Sharepoint list, • Windows Azure marketplace, • any hadoop file (hdfs), • hdinsight, • active directory, • facebook. • Exchange ** New • RDBMS: sql server, azure sql server, access (oops), oracle, db2, mysql, postgress, teradata • File: • excel, csv, txt, , XML, JSON, folder (metadata) • From the intarwebz! • Write an M or power query formula language query yourself from scratch

  11. sources • Regular sources—most database platforms including hadoop • Files—all types • Odata • Web site tables! • Active directory!

  12. Functions • Transformation! • Merge (join on key) • Append—combine sources to new destination

  13. Create your own function

  14. Create your own function Goes To symbol

  15. Resources • http://office.microsoft.com/en-us/excel-help/learn-about-data-explorer-formulas-HA104003958.aspx Formula cheat sheet • Great post by datapighttp://datapigtechnologies.com/blog/index.php/cool-things-you-can-do-with-data-explorer/ • On M language: http://blogs.msdn.com/b/modelcitizen/archive/2010/09/22/update-on-sql-server-modeling-ctp-repository-modeling-services-quot-quadrant-quot-and-quot-m-quot.aspx • M language specs from Microsoft: http://msdn.microsoft.com/en-us/library/dd285271.aspx • http://blogs.msdn.com/b/mlanguage/ • Downloadable 200 page doc on formula language (M) http://go.microsoft.com/fwlink/?LinkID=235474&clcid=0x409

  16. Resources from Rob (more recent) • https://social.technet.microsoft.com/Forums/en-US/0f9fec02-4469-4c56-92ec-00d46b26b3fc/how-to-automateparameterizeloop-power-query?forum=powerquery • http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/ • https://office.microsoft.com/en-us/excel-help/learn-about-data-explorer-formulas-HA104003958.aspx • https://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx?CTT=5&origin=HA104003958http://thatmsftbiguy.com/powerqueryexcel/

More Related