@skillwave.training
Your #1 resource for online training on Power BI, Power Query, Power Pivot, and Excel. View our course catalog at skillwave.training/learn
@skillwave.training#PowerQuery doesn't have a great concept of row context, making it very difficult to compare values across rows. 🔢 In this Monkey Shorts, @excelguru.ca demos a super-cool black magic technique that allows you to do exactly that. 😈 www.youtube.com/watch?v=zdNY...
@skillwave.trainingThis is the kind of hands-on troubleshooting AKA members get every session, using the messy data they actually work with. Subscribe to the AKAs today at skw-t.com/aka, or get access included with our #SelfServiceBI (skw-t.com/ssbi) or #Excel Fundamentals (skw-t.com/excelfun) Academies.
@skillwave.trainingHis fix is to deal with it early. Right after promoting headers but before setting data types, replace "NaN" as text. If it has already been converted, change the column to text, replace the values, then convert it back. 🧭Takeaway: Clean up problem values before you set data types, not after.
@skillwave.trainingHe found something interesting. Set the column to Decimal Number and NaN becomes a true "not a number" value; set it to Whole Number and it becomes an error. Replace Errors catches the error but not the NaN, and there's no built-in function to replace NaN. 😮
@skillwave.trainingOne of our Ask Ken Anything participants wanted to know how to handle Not A Number (NaN) values in a CSV exported from their resource planning system. Replacing them seemed to work, yet the query still reported errors that came up blank when clicked. 😠
@excelguru.ca tested it live in #PowerQuery...🧵
@skillwave.trainingYesterday was Thanksgiving for our team in Canada, and we've woken up from our turkey coma enough to send out the latest newsletter. 🦃
If you're not subscribed, sign up at skw-t.com/news for info on what's new in #Excel and #PowerBI, courses, events, specials, a free #PowerQuery eBook, and more!
@skillwave.training🕒Time intelligence is the collective name for patterns that can be used to solve time comparison problems, such as current VS previous month's sales. 📈 If you do time-based comparisons using #DAX, #Excel #PowerPivot, and/or #PowerBI, this article is for you: exceleratorbi.com.au/dax-time-int...
@skillwave.trainingWith an average rating of 4.88 ⭐ / 5, students LOVE Power Query Academy!
Want to focus on the #DataAnalysis you were actually hired for? Stop repetitive #DataCleansing and #DataPreparation, and start gaining more data insights. 📊🔍
Enroll today at skw-t.com/pqa
@skillwave.trainingA really great read for anyone who works with #PowerBI.
@skillwave.training#PowerQuery's fill down ⬇️ /fill up ⬆️ feature is amazing, but what if you need to "un-fill" a selection for some reason? There may be an un-fill command coming to #PowerBI, but it's going to be some time before we see it in #Excel. Here's @excelguru.ca's method: www.youtube.com/watch?v=yHwt...
@skillwave.trainingHere, @excelguru.ca explains the types of blanks that can show up in #PivotTables built from the #DataModel. 🙃 If you work with #Excel PivotTables, #PowerPivot, #DAX, or #PowerQuery, this will help you understand where blanks come from and how to deal with them: www.youtube.com/watch?v=fnVU...
@skillwave.training🧭Takeaway: When time data won't compare cleanly, make sure you’re using a common data type!
Bring your trickiest data puzzles to the AKA and see them solved. Enroll in our #SelfServiceBI (skw-t.com/ssbi) or #Excel Fundamentals (skw-t.com/excelfun) Academies to gain access, or at skw-t.com/aka
@skillwave.trainingThe breakthrough was one small step: The time tracker stored durations, which turned into tiny, meaningless decimals next to the project hours. Using Transform > Duration > Total Hours converted 5 h, 59 m, 50 s into 5.997 hours, making the two systems directly comparable. 💡 No dividing by 24 and 60!
@skillwave.training1️⃣ Clean both sources.
2️⃣ Replace "0:00:00" entries with nulls so empty records wouldn't clutter the #PivotTable.
3️⃣ Build shared employee and calendar tables
4️⃣ Write measures to calculate the variance.
@skillwave.trainingA question sent in for our recent 'Ask Ken Anything' (AKA) session tackled a common reporting headache: how do you compare hours logged in a project management system against data from time-tracking software? 🕒
@excelguru.ca walked through a solution he built using #PowerQuery and #PowerPivot. 🧵
@skillwave.trainingUnlock the power of #DimensionalModeling by learning the right way to build solid and scalable Data Models in #Excel and #PowerBI.💪 This course pairs #PowerQuery and the #DataModel together using each for what they were designed to do.
➡️Transform your #BusinessIntelligence skills: skw-t.com/modeling
@skillwave.trainingFollowing up on the Monkey Shorts episode shared yesterday, here's our #PowerQuery Recipe card that outlines the steps to number your data by grouped rows. 👩🍳 You can get the full collection (over 95 handy recipes!) at skw-t.com/pqrecipes
![Master Your Data in Excel & Power BI
Pattern Difficulty: Lo Code
Number Grouped Rows Recipe
Initial Preparation
Sort any [Dimension] column(s) to group the data
Sort the [Fact] column -> Descending
Re-apply the column data types
Group the Data
Go to Transform -> Group -> Advanced
Group by the [Dimension] column(s)
Add a new column called “Data” -> All Rows
Add Row Numbers
Go to Add Column -> Custom Column
Name: Custom
Formula: =Table.AddIndexColumn([Data], "Index", 1, 1)
Right click the [Custom] column -> Remove Other Columns
Expand all columns from [Custom]
Re-order the columns if desired
(Image of a Source Table containing data first listed by Group, then category. A second Table With Numbered Rows image shows the addition with an index column where each Category within a Group is numbered sequentially by row.)
DBAs know this output as using SQL’s ROW_NUMBER with PARTITION
For more data patterns, check out our courses at skillwave.training](https://cdn.bsky.app/img/feed_fullsize/plain/did:plc:ihdq52obivvf6sq3lnalnwrv/bafkreic3owldkbs2x3kxujjl3qafxvataxrkyjyc2lvcrqdbmhc5j7r3x4@png)
@skillwave.trainingAKA "number rows by partition" this is a useful recipe to have in your #PowerQuery arsenal. The basic goal is to provide each item in a group with a row number, restarting the numbering at row 1 when we move to the next group. 🔢 @excelguru.ca shows it's very easy: www.youtube.com/watch?v=9ckP...
@skillwave.trainingFinal takeaway: Take the output of #PowerQuery, feed it back into itself, and build a self-growing forecast history system—just make sure to remove duplicates before appending.
If you work with forecasts, budgets, or projections that change over time, this is a simple but powerful pattern. 👍
@skillwave.trainingHow do you track a weekly forecast, preserve history, and turn it into a meaningful waterfall view—without doing everything manually? 😫
In this video, @excelguru.ca tackles a real student question: www.youtube.com/watch?v=79Ui...
#DataAnalysis #BusinessIngtelligence #DataVizualization #Excel
