I don’t know about you, but my goodness, wouldn’t it be great to press Close & Apply on Power Query and instantly have all of your columns documented in your model based on metadata definitions in your query? And shoot, why stop there? Let’s just leave the column titles exactly as they are from the […]
Read more "That’s So Meta"Dirty lakes need cleaning, simple as that – and it’s no different when it comes to Dataflow Gen2’s staging feature. You might be wondering, “What’s the big deal?” Well, let’s dive into and dissect the backend staging setup of dataflows in the Lakehouse. Close your eyes and imagine countless system tables, each tagged with lengthy […]
Read more "Cleaning the (Staging) Lakeside"If it’s important, you should probably be logging it. With KQL Databases and the newly introduced Semantic model refresh activity within Data pipelines, its just way too easy (2EZ) now. Before we dive in, let’s look at a few prerequisite items needed for this article: After you have added a Semantic model refresh activity (how to) to the data pipeline […]
Read more "From Pipelines to Table: The Outputs We Ingest"Data Factory in Microsoft Fabric is an AMAZING tool that allows us to combine the flexibility of Data pipelines and ease of a Dataflow Gen2 to create some nifty solutions with a tiny smidge of code. One of the most common use cases is the ability to load new data into a destination for a […]
Read more "CHANGE (IN THE HOUSE OF LAKES)"I’ll always be the first to admit that the things that interest me may be of little (technical) interest to you. However, I figure “Hey, why not just start documenting my random gibberish findings?!” and well, here we are with a short but sweet article on Dataflows Gen2 in Microsoft Fabric. Backstory: I’ve always been […]
Read more "It’s a LongType() to the top (If you want to rock and roll)"This entire article was generated using Bing‘s GPT, in somewhat of a weird experiment to see how it would respond to the prompt – “Can you provide me a blog post idea about teaching someone how to learn the data types in the Power Query formula language, using complete paragraphs to describe each suggestion” – […]
Read more "5 Tips for Learning Data Types in Power Query Formula Language"There’s only one rule. Don’t break the fold! Before we start enforcing all these rules, we should start with something like a solid foundation of what does and doesn’t fold in Power Query and their SQL equivalents. Now, if you found this page and we’re like “what in the world is query folding?” read this […]
Read more "The (Almost) Definitive Guide to Query Folding"There is a bombardment of information constantly vying for our attention – mobile phones, smart TVs, that 1996 Space Jam website and of course endless cat videos, which is why I believe the advances in low-code automation have been an incredible benefit for sharing (the most relevant) information with others. And one of my favorites […]
Read more "Yo, Listen!"As the war wages on between #TEAMVLOOKUP and #TEAMINDEXMATCH, a new challenger has arisen. #SUMPRODUCT, a function often associated with math and trig, has now taken on a new purpose – Boolean logic. But can it compete? Or, will it continue to lurk in the shadows, only to be utilized by Power Users… Download Workbook […]
Read more "I Always Feel Like SUMPRODUCT()’s Watching Me"Being the 115th ranked general Excel blog on the internet sure has a nice ring to it. Well deserving of your e-mail subscription (located in the bottom right hand corner) if I was to be rather James Blunt about it. But what good is a rank if there are multiple categories to be considered? “Voted best burger […]
Read more "I’m Unsure How Official This Ranking System Really Is…"If repetition is the key to mastery, repeating an activity the wrong way is a killer in disguise. A countless waste of hours dedicated to a mundane task. Fortunately, Excel is intended to avoid such things, and the distance between getting it done and being a master is much shorter than you may realize. killer filler [kil-er] verb, Slang task or activity, that is particularly […]
Read more "All Filler – No Killer"Undoubtedly you have called a function a formula and a formula a function; it’s just the nature of the Excel-lingo beast. That stops now. I want you to see functions for what they truly are: computer programming. You may feel like you are not yet ready to call yourself a computer programmer – but you are. And I […]
Read more "What The Func?!"The founding fathers of Multiplan intended that spreadsheets would be for everyone: the rich and the poor, the data analyst and the data scientist, the sighted and the vision impaired. Often, things that we take for granted make a world of difference to others. Take the appropriate steps now to ensure that your work is up to accessibility standards. Download Workbook Add It: Navigate to […]
Read more "Make Spreadsheets Accessible Again!"I saw you from across the Office through the watch window. You were sitting at the table with the perfectly cleansed data. I wanted to approach but just didn’t know the correct queries to ask. I know we would have an instant connection – if you see this, send me your ODC file. post id: 6988676976 posted: 2016-07-15 […]
Read more "Missed Connections"It’s Dangerous To Go Alone! Take This. DOWNLOAD WORKBOOK If you find yourself becoming a mindless drone, constantly wondering when it’s the weekend – then it’s time to add to your arsenal. Returning the calendar work days quickly can be an incredibly powerful solution to overcome this challenge, but only when used correctly. Function: WORKDAY Describe It: […]
Read more "I Have Come Here To Chew Bubblegum and Kick Axis… And I’m All Out of Bubblegum"“What gets measured gets managed.” Peter Drucker Analysts estimate anywhere from 50-80% of their time is dedicated to cleansing information (Source: New York Times). Making use of Excel’s data validation controls can drastically reduce this necessary evil when developed properly. No longer will you spend hours trying to infer if “S” really means “Saturday” or “Sunday”; […]
Read more "Love It Or List It"With every passing second of every minute, of every hour – you are in a constant race against time. Trying to find time to exercise, to spend time with friends or family, or read the latest Excel TV article. Managing your schedule becomes even more important in the age of information. More data means more analysis, […]
Read more "You Think You Have Time"Not since the great territory battles of 7-Eleven Vs. Quiktrip, has there been such an unparalleled rivalry: “VLOOKUP or INDEX/MATCH?“A seemingly innocent question, when asked among Excel users, gives you a general perception of one another. There is one function’s inability to turn left and the others reliance on multiple functions for use. One’s perceived simplicity vs. the others perceived complexity. Are […]
Read more "Defining The Great Divide"This is not an Excel Lesson… This is a Lesson About Excel. Let’s start at the beginning… Download Workbook Day 1 Function: SUM Describe It: Add all the numbers within a range of cells. Syntax: =SUM(number1,number2,…) number1 can be anything from a single cell to a range of adjacent cells number2 and so on can be anything from […]
Read more "500 Days of SUM(SUMIFS())"piv·ot·al ˈpivədl/ Of crucial importance in relation to the development or success of something else. Synonyms: central, crucial, vital, critical, focal, essential, key, decisive There is no such thing as “the right time” when it comes to developing your talents in Excel. You are always on call as the swiss-army-knife of information. Today a financial analyst, tomorrow […]
Read more "That Pivotal Moment"Information is not an argument, it’s a conversation. As a developer, having the ability to remove personal feelings about what you think a workbook should be will take you far in you career. Always be mindful that your end users may simply want the freedom to drive their own story in an easy-to-use format. And that’s perfectly fine. Not […]
Read more "The Best Truth Is Data"“If you can’t explain it simply, you don’t understand it well enough.” -Albert Einstein It started out innocently: every year needed its own tab, then every month, then every day. Before you knew it, finding “that one tab” turned into Legends of The Hidden Temple with Kirk Fogg yelling directions at you as you feverishly click about. I […]
Read more "For Inquiring Minds"In a world of instant gratification and “I need this done yesterday,” it’s difficult to consistently check your work. Being the mad data scientist that you are, you would prefer to double-, triple- and even centuple-check your workbook before releasing it into the wild. Well, what if I told you that you could be both thorough and efficient? […]
Read more "WATCH OUT!"“If I paint something, I don’t want to have to explain what it is.” -Bob Ross A common theme you will hear me discussing is storytelling. “They” say a picture is worth a thousand words, and the human eye is receptive to shapes and colors as a means for detecting object variances. That’s why telling a visual story is so […]
Read more "You Don’t Owe Anyone An Explanation"You will never learn more about Excel than on the day you inherit a broken workbook. You’re tasked with uncovering the method to the original author’s madness, and if you’re not careful you can get lost trying to understand why the author did things the way they did instead of finding the solution. Error Type Description #NULL! When a […]
Read more "To Err Is Computational"We all have problems. Excel is an executable. No more or less than 1’s and 0’s interpreted by your operating system. To its unsuspecting users it is perceived as a daunting application full of things that you keep meaning to learn more about. Just stop. Excel is not the solution to your problems, you are. Without an answer […]
Read more "We Need To Talk"Until you’ve tasted the sour you can never truly enjoy the sweet. Let’s look at two varying statements objectively – Excel is a great application because of its incredible ease of use. Excel is a terrible application because of its incredible ease of use. Getting started is as easy as clicking in a square box and […]
Read more "The Illusion of Movement"“Without villains, there can be no heroes.” Our society is suffering under the constant oppression of dystopian workbooks – riddled with mediocrity and confined to restrictive methods. We need a hero. Someone to write the wrongs and be the protagonist in our story. We need a Mad Maximum Value. Great workbooks stand the test of time. This is a fact. The more you continue to work […]
Read more "Mad Maximum Value"“I don’t know what you’re making me do and I have no idea when I will ever use this.“ Good. That means you’re on the right track, because I need you to look past the obvious and start concentrating on the possibilities. Let’s consider this idea: 50% of Excel’s abilities have to do with inputting text and numbers. The other 50% have to […]
Read more "Wait a Minute Doc. Are You Telling Me That You Built a Time Machine Out of a Spreadsheet?"In our last lesson we talked about following the laws of fields and records. Now is the time to expand on that thought process because everything you knew about cell ranges and formulas is about to get turned upside down with two simple keyboard strokes. Right now is your come-to-Jesus moment. You have been abusing yourself managing […]
Read more "The Million Dollar Bracket"There’s nothing better than releasing the final version of your workbook – every bit of sacrifice, blood, sweat and coffee stain for the world to bask in its majesty, for small children to sing songs about and draw pictures of in kindergarten classrooms. Having finally realized Maslow’s theory of self actualization in this single project […]
Read more "A Five Star Dinner On A Table With A Broken Leg"