Ken Puls’ Post

President and Chief Training Officer at Excelguru Consulting Inc.

If you work with large data sets in #PowerQuery in either #Excel or #PowerBI, you'll know the pain that comes with waiting on query previews to refresh. 😫 In this Monkey Shorts video, I share my strategy to quickly flip between a smaller and larger data set so that you can develop with speed, and then flip the switch to apply your logic to your entire data set. 🧐 Check it out here: https://lnkd.in/gy_vY9J5

3 Comments

Transcript

This week I'd like to look at a handy trick in order to help you reduce the development time when you're working with large data sets. So let's go look at my Power Query here I've got my file path, and you'll notice it's pointing to a SharePoint site. And on here I've got a couple of Excel files. I've got Excel file prod, which is Chitsa, which holds about 350,000 records. And that takes a little while when you're hitting your refreshes to actually come through when you're making manipulations. So the first thing I did is I grabbed that data, filtered it to just December 2013. Noted it to a worksheet and saved that as a separate excel file. OA snapshot at a much smaller data set out of my global set and I've saved that here as 2013. And this is a look at the two files cheats dev about 500 rows of data here for justice December 2013 and chips rod that has all of my data back to 2009 O. What I'm going to do now is I'm going to build myself a nice new little parameter here. We're going to go manage new parameter, and I'm going to make myself a new parameter called data set. There we go. I'm just going to go and paste into description. Do you want to work with dev or prod? And then this is going to be text values, and I'm going to give it a list of two. Dev or prod, There we go. The default value value is going to be prod, but I'm going to set my current to dev for my development set and we'll say OK. Now what I'm going to do is I'm now going to go and I'll just reference chips prod here we go reference and call this one here Chips under score staging. And you'll notice that it's referring to Chip's rod with our 2009 date O. What I'm going to do is I'm going to do this now if my data set equals dev. Then we're going to go with. Cheats dev, else we're going to go with chips prod. So if you don't get the dev exactly right, you're going to get the production data set. When we hit enter, notice that it now changes to the smaller data set. And I can toggle this very, very quickly by just changing this out to prod. And right there we're back to our 2009 set. And this is super, super handy because now while I've got this set to dev, my refreshes will come back a lot faster. My preview refreshes as I'm doing more complicated things. Because we're only working with a 1500 row data set, and when I'm done, I switch it back to ROD. If I'm doing this in Power BI, I publish it to the service and I can actually override that setting right away in the Power BI service to give the production data set. It's all formula, firewall safe, Everything works beautifully. So hopefully that's a trick that will help you speed up development time. Granted, you do have to make that separate file, but I think you're going to find it's worth it if you're working with big datasets. Thank you for watching this episode of Monkey Shorts. If you enjoyed it, please don't forget to click on the Skill Wave logo on the bottom left in order to subscribe to our channel. Or if you'd like to see more videos in the series, click on the playlist tile on the right. And if you'd like to get more comprehensive training, you should definitely check out our website at Skywave dot Training.

Dawid Sebastian Gałęzyka

Dynamics 365 F&O × FlexProperty × PowerBI × Excel × Data Science

Definitely great tip. I am using similar approach, just have Debug parameter true/false. Then in staging if Debug is true, keeping top X rows or for example when connecting to OData adding $top parameter to limit network traffic during development.

Willem Eek QC

Financieel interim manager Finance & Control / Bedrijfsvoering Niet meer beschikbaar!

Well said!a a

Sherry Barnes-Fox, MBA

Love this!

See more comments

To view or add a comment, sign in

More Relevant Posts

Skillwave Training

1,351 followers
1mo
Report this post
If you work with large data sets in #PowerQuery in either #Excel or #PowerBI, you'll know the pain that comes with waiting on query previews to refresh. 😫 In this Monkey Shorts video, Ken Puls looks at a strategy to quickly flip between a smaller and larger data set so that you can develop with speed, and then flip the switch to apply your logic to your entire data set. 🧐 Check it out here: https://lnkd.in/gMgNMvYK
Like Comment
To view or add a comment, sign in
Excelguru Consulting Inc.

1,106 followers
4w
Report this post
If you work with large data sets in #PowerQuery in either #Excel or #PowerBI, you'll know the pain that comes with waiting on query previews to refresh. 😫 In this Monkey Shorts video, Ken Puls looks at a strategy to quickly flip between a smaller and larger data set so that you can develop with speed, and then flip the switch to apply your logic to your entire data set. 🧐 Check it out here: https://lnkd.in/gMgNMvYK
Like Comment
To view or add a comment, sign in
Kumaraguru Ethiraj

Aspiring Data analyst | Excel | Power BI | SQL |Python |Tableau | AI
3w Edited
Report this post
🔍 𝐅𝐚𝐜𝐢𝐧𝐠 𝐢𝐬𝐬𝐮𝐞𝐬 𝐰𝐢𝐭𝐡 𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐚𝐧𝐝 𝐄𝐱𝐜𝐞𝐥? 𝐇𝐞𝐫𝐞'𝐬 𝐚 𝐪𝐮𝐢𝐜𝐤 𝐟𝐢𝐱! 🚫 𝐈𝐬𝐬𝐮𝐞: Ever encountered a "file in use" error in Power BI, preventing access to your Excel data? This typically happens when the file is open in Excel. 💡 𝐒𝐨𝐥𝐮𝐭𝐢𝐨𝐧: 1️⃣ 𝐂𝐥𝐨𝐬𝐞 𝐭𝐡𝐞 𝐄𝐱𝐜𝐞𝐥 𝐟𝐢𝐥𝐞: Ensure the Excel file you're using isn't open. 2️⃣ 𝐂𝐨𝐩𝐲 𝐭𝐡𝐞 𝐟𝐢𝐥𝐞: Make a duplicate and save it in a different location. 3️⃣ 𝐔𝐬𝐞 𝐭𝐡𝐞 𝐜𝐨𝐩𝐢𝐞𝐝 𝐟𝐢𝐥𝐞: Connect Power BI to the duplicated file instead. 🌟 This simple workaround ensures smooth data access in Power BI without interruptions! 📊💻 Follow me this kinds of tips here. 𝐋𝐢𝐧𝐤𝐝𝐢𝐧 :https://lnkd.in/dbkkjZw5 𝐌𝐲 𝐆𝐢𝐭 𝐇𝐮𝐛 :https://lnkd.in/dq_8ngRX #PowerBI #Excel #DataAnalysis #TechTips #LinkedIn #Codebasics #Powerquery #Learning

2 Comments
Like Comment
To view or add a comment, sign in
Ali Koudar

Master of Data: Expert in Data Cleansing, Analysis & Visualization with Power Query, Power BI and Tableau
5mo
Report this post
Power Query in Excel is a game-changer! It's like having a data assistant at your fingertips. 🌟 I've used it to merge data from different sources flawlessly. Have you ever tried combining data with Power Query? Share your experiences! #PowerQuery #DataIntegration #Excel #DataPrep

1 Comment
Like Comment
To view or add a comment, sign in
Ashish Mathur

Founder, Excel Enthusiasts
2mo
Report this post
In PowerBI, show departments that did not exist in the previous month https://lnkd.in/gzcSQ7Bs #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

Re: Excluding Departments Not Present in Last Month's Data in Power BI

community.fabric.microsoft.com
Like Comment
To view or add a comment, sign in
Jesus Martin Adrados

SAM Analyst | Licensing Geek | Microsoft Certified
8mo
Report this post
I have just finished reading Ken Puls & Miguel Angel Escobar's book "Master Your Data with #PowerQuery in #Excel and #PowerBI" (formerly 'M is for Data Monkey'). It has helped me understand more advanced scenarios, the inner workings of the tool, and best practices, in addition to giving me new ideas and the confidence to build more of my own solutions in Power Query. A must-read!
6 Comments
Like Comment
To view or add a comment, sign in
Yogesh Pandey

Director Vistashiksha solutions pvt Ltd
2mo
Report this post
🔍 Excel Power Query Tip: Extract Names and Numbers Effortlessly! 📊 Hello, LinkedIn community! Power Query in Excel is a powerful tool for data transformation and analysis, but did you know you can also use it to extract specific data such as names and numbers from a mixed column? Whether you're cleaning up data or preparing it for analysis, Power Query can make the process seamless. Let’s continue to empower each other with practical Excel tips and tricks! #Excel #PowerQuery #DataTransformation #DataCleaning #DataAnalysis #ExcelTips #MicrosoftExcel #DataPreparation #ProductivityHacks
Like Comment
To view or add a comment, sign in
khushtrin kashfi

Data analyst in transition | piping professional seeking analytical challenges
7mo Edited
Report this post
Creating a date table in Power Query is a common practice in Power BI to facilitate time-based analysis. Here's a step-by-step guide to creating a date table using Power Query: 1. Open a blank query in Power Query of Power BI 2. type ={Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))} 3. That will generate a series of numbers as a list 4. Convert it to a table (upper left menu button). 5. Convert the ABC123 type to date 6. Rename to Date. 7. Now you have a date table. Add columns as necessary (year, month, month name, etc) to make your date table suit your needs. 8. Close and load. 9. Right-click on it and mark it as a date table Happy Learning #PowerBI #DataAnalysis #TimeIntelligence #PowerQuery #DataVisualization #BusinessIntelligence

2 Comments
Like Comment
To view or add a comment, sign in
Ashish Mathur

Founder, Excel Enthusiasts
11mo
Report this post
In PowerBI and MS Excel, extract data from multiple tables of multiple PDF files lying in a folder https://lnkd.in/da4f_geC #powerbidesktop #powerbi #powerquery #microsoftexcel #msexcel #excel

Re: Need Recommendations on Pdf Data Set

community.fabric.microsoft.com
Like Comment
To view or add a comment, sign in
Syed Nishad Anjum

Trainee at ExcelR solutions, Hyderabad, Telangana
4w
Report this post
Power Bi dashboard: Financial analysis of complex data and insights click on the below link: <iframe title="Financial Dashboard Analysis" width="600" height="373.5" src="https://lnkd.in/gJzW2Ddj" frameborder="0" allowFullScreen="true"></iframe>
Like Comment
To view or add a comment, sign in

9,395 followers

View Profile Follow

Ken Puls’ Post

More from this author

New GROUPBY and PIVOTBY Functions

Explore topics