If you haven’t read the first two posts (Part 1 | Part 2) in this series yet, I welcome you to do so before reading this one. I also recommend that you check out this post on Query Error Auditing so you get a better understanding of what types of errors you can find in […]
In the previous post I showed you guys how to create a conditional column in Power BI / Power Query using the UI and then just using the Power Query Formula language.
In this post we’ll go over the available conditional operators and how to do Nested IFs in Power BI / Power Query.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
If you come from Excel, you’ve probably seen or heard about the IF statements and its new sister the IFERROR.
I remember the first time that I saw a conditional chain like the picture below:
It looked WAY better as a diagram than as an Excel formula, nevertheless – it worked just fine inside of Excel.
The question is….how do Conditionals work in Power BI / Power Query? do we have an IF function? maybe an IFERROR? THIS is the blog post where I’ll cover this topic.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
Over a year ago I wrote a blog post in this specific topic. I highly recommend that you read it (here) before you read this one. The reason why I’m writing kind of a follow up on this it’s because I recently had a really heated conversation with a very close friend of mine who […]
For the better part of the last 2 years, I’ve been most of my time working “on the field” getting to know each and every user persona of Power Pivot, Power Query and Power BI in general.
This is one of the reasons why I didn’t post that much during the 2016-2018 period. I did a full research on my own to better understand the user personas, what their pain points are and how to better reach these with techniques and patterns that are applicable to them.
I’ve learned a lot from these people and one of the main situations that most new Power BI users that come from Excel face is the fact that they try to tackle their scenarios the same way that they would tackle them inside of Excel, which usually prevents them from taking the full advantage of what Power BI has to offer and at times it makes them waste way too much time in their initial steps because of preconceived ideas.
In this post I’d like to talk about the main scenario that I see and it has to do with overusing DAX for any situation that you can think of, why this happens and start the conversation on how you can avoid placing yourself or any of your colleagues in this position so you can fully take advantage of what Power BI has to offer which goes beyond just DAX.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
This is going to be the last post series in the series on Combine or Append Data.
In the first post we saw the basics of how to do the Append operation through the UI.
In the second post we saw the Combine Files experience with Flat Files and how easy it is to combine as many files as you want.
In the third post we had a contrast of the Combine Files experience using Excel Workbooks instead of simple flat files and what things we needed to consider this time that we didn’t consider with simple flat files.
In this fourth and last post we’ll be going back to the basics using the function that we discovered in the first post – Table.Combine which is the most optimal function for combining / appending data.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
In the previous post we saw how we were able to combine multiple files from a Folder.
In that post we were using flat files but, how would that process be for Excel files?
This is the post where we’re going to see the difference between simple flat files and more complex files (like an Excel workbook) when it comes to using the Combine Files experience inside of Power BI / Power Query.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
In the previous blog post, I went through the basic concepts behind the Append operation found in Power Query for Excel and Power BI.
In that post, we only used 2 files and it was pretty straightforward to simply click the Append queries button to combine both queries like so:
but what happens when you have multiple files? Let’s say 12 files. 1 for each month of the year.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
I’ve previously done a series on Merge / JOIN operations (First Part here) and it’s now time to do one on Combine / Append operations.
so…How do you combine / append / stack tables with Power BI / Power Query?
There are multiple ways to accomplish this, but we’re going to start with the basics.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
I’ve been trying to join multiple Facebook communities that revolve around Power BI topics.
I was able to join a couple communities that are completely neutral in the sense that they’re not run by a for-profit company, but rather just community members which make things easier as there’s little chance of a conflict of interest with the admins of the group.
One of these groups is called “Power BI Latinoamerica” which is a Community that primarily speaks the Spanish language and within that group one of the admins posted a video that caught my attention:
It’s basically a video that showcases a way to connect to an Excel file that is being hosted on OneDrive and while that method is completely valid, I was trying to reference the author of that video to one of my articles about connecting to files hosted on SharePoint and OneDrive and then I realized that I haven’t formally wrote about that topic in my blog…ever.
Disclaimer, I’ve created multiple videos about this for some of my online courses, so you might’ve seen this method before if you’ve followed any of the courses where I participate.
It’s time to change that! Let’s find out what’s the easiest and most optimal way to connect to ANY file hosted on OneDrive or SharePoint.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
A few years ago (in march of 2013!), I wrote a guest blog post on Rob’s blog (PowerPivotPro) on how to do relative date filters. The title of that post was “how to always show Yesterday, Today, or Tomorrow’s Data” with DAX in PowerPivot back in Excel. The post tried to address the issue that […]
A long awaited post! The Fuzzy matching preview feature was added to Power BI Desktop MONTHS ago and here’s my take on it.
What is Fuzzy Matching? In short, it’s an algorithm for approximate string matching.
Why does it matter? Up until September of last year, Power BI / Power Query only gave us the option (natively) to do Merge / JOIN operations similar to a VLOOKUP (FALSE) where we can only do exact matches. That has changed and we can do now “close” or “approximate” matches thanks to Fuzzy Matching
What can I do with it? Let me give you a practical example of something that I recently had to do!
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
