Monday, February 13, 2023

Create a Calendar Table From Date to Today


When you’re analyzing data where you’ve got dates, sometimes it’s a good idea to create a calendar table that not only has a date, but single column fields for the year, month or day of week. It helps then to show more detailed in your data. You can create one directly in the Excel worksheet with functions or you can use Power Query to make it more dynamic like creating a rolling calendar to auto populates with the current date. Creating a calendar table is not that hard and it’s useful information to help you do some additional analysis if you’re comparing dates or want to do some date grouping in a more granular way. It’s takes little time to set up and it’ll be useful in the long run.

Monday, February 6, 2023

Combine and Unpivot Tables Multiple Worksheets Same File


You have multiple worksheets or tab to combine?  It's easy if its just 1 or 2.  It's even not that bad if you have to do some kind of transformation like to unpivot the table.  However if you have to do a LOT of tables or do it on a recurring basis, then it's becomes a hassle.  Power Query can rescue you from the mundane and laborious process of manually doing these steps.

Monday, January 30, 2023

Combine & Unpivot Tables Multiple Excel Files in Folder


Getting files from your co-worker or from a system and need to combine or join them together? Easy to do if you have a few file; it's a simple copy and paste. Even if you have to transform you data by unpivoting your table it's not a hassle. But if you've got over five files or it's a recurring process, it becomes a burden to do. Power Query can do all this in a semi automated way to solve your problem.

Monday, January 23, 2023

Combine and Unpivot Tables Multiple Worksheets Power Query Function

When you have multiple worksheets or tabs to combine together, it's not a difficult process to copy and paste. However if you need to transform the table in those worksheets, like unpivoting it; still is not that bad albeit it'll take a bit more work to transpose the columns and rows. Now if you had to do this to a LOT of worksheets or if you had to do this on a recurring basis, it becomes a pain to do this. The solution is to have Power Query automate these steps; and it can future proof it by incorporating new data.

Monday, January 16, 2023

Use Power Query Parameter to Quickly Filter


This video will introduce you to using the parameter feature in Power Query. You can use parameter to pass values to other parts of your query to give interactivity to your query. This example will use a simple procedure of filtering a column to show how it works. Now filtering a column seems like overkill, but if you have a LOT of columns, scrolling across the many columns can be a hassle. Incorporating the parameter feature may make it less of a hassle.

Monday, January 9, 2023

Running Total in Power Query


Creating a running total in Excel is easy, really easy if you're an intermediate Excel user. Why then would we consider doing this in Power Query? Maybe it's a small part of a series of steps you're doing to clean up data. In that case you don't want to have a running total calculation outside of your Power Query steps. Unfortunately there is no Ribbon command in the Power Query editor to perform running totals. Don't worry, it's actually quite easy to do a running total in Power Query. It's just some M code and it's not even a lot.

Monday, December 19, 2022

Sharing Files with Power Query Parameter Feature


When you are sharing Power Query files, it's usually not an issue to update and refresh your data. However if your source file and your file that is the final output for analysis are separate files, it becomes an issue. You're sending two separate files to your co-workers and expect this to work? Good luck, unless you have an easy way to make your links to the source file link. That could be done fairly easily with a parameter. Check out the video to see how you can make sharing Power Query files seamless.