To my Power Query / Power BI Friends.
You too can deliver production-ready robust queries vis MS Power Query if your avoid "The Ten Pitfalls of the Data Wrangler..." I came across this article that you might find of interest. I know that I have personally discovered some of these issues.
https://powerpivotpro.com/2017/02/the-ten-pitfalls-of-the-data-wrangler-aka-the-wielder-of-mpower-query/
Great article Tom, thanks!
Recently used Power Query to help with proofing 115 performances. Used SSMS to get the data out, and then transformed it with Power Query. Helped with proofing (per performance) Price Type amounts, Ticket text, Content tab of performance, and initial Availability of tickets per performance. After building the performances, ran the SSMS queries, pasted the updated data into the Excel docs, hit Refresh, transformed data within seconds. When we do this performance run next year, just need to paste the new data, click Refresh, and start proofing. Love it!
Neil Cole would love to hear more about the SQL code you used.
Are you on Local Tessitura or using RAMP. If you are on Local Tessitura you can do one better by creating a spreadsheet that goes and gets the needed data without Copy and Paste.
Tom Brown, we are on RAMP. Although, it would be awesome to get the data by just making a connection to the database from Excel! It might be a while before I can get the SQL scripts to you. Lately, I’m stuck working away from my desk for most of the day. I’ll try to put something together to show the queries and summarize the end results.
I’m just glad I happened to run into Power Query while watching YouTube (way faster to digest than the books I have). I can now just develop a SSMS query that gets me the data into a format that will work with one of my Power Query queries. Then Copy, Paste, Refresh, Save As, done.
For those interested, if you have Excel 2010 or 2013, it is called Power Query and has its own separate tab. Although, not all 2013 versions have it. If you have 2016 or after, it is on the Data tab, Get & Transform section.
Here is the BEST YouTube channel I’ve found regarding Power Query, Power Pivot, and Power BI
(I kept running into his videos and then I finally clicked the profile; then my jaw dropped looking at all the videos.)
ExcelIsFun
Includes links to downloadable files so you can follow along, PDF of notes (well done notes), and time stamped outline of topics (so you can easily get to the part you want)):
FREE Excel Education — 3,000 Excel videos — 100 Playlists
https://www.youtube.com/playlist?list=PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT
Data Analysis and Business Intelligence Made Easy with Excel Power Tools. Excel Data Analysis Basics (E-DAB).
https://youtu.be/FLzKnNmE4Ms
E-DAB 06: The Magic of Power Query to Import, Transform, & Load Data
https://youtu.be/ldoQws7Zbx8
Excel Magic Trick 1331: Import Excel Files & Sheets into Excel: Power Query Get & Transform
Excel is Fun is wonderful and the Mr. Excel Podcast is also good as well.