Tag Archives: Merge

  • Connecting to Files in SharePoint & OneDrive with Power BI / Power Query

    image

    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.

  • Fuzzy Matching in Power BI / Power Query

    image

    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!

  • Merge Operations in Power BI / Power Query – Part 6: Full Outer Join

    image

    This is the last post in the series! I highly encourage you to read Part 5 of this series before reading this one, but nevertheless, you can jump right in if you know the basics of Merge / Join Operations inside of Power BI / Power Query.

    We will be using the same sample data that we used in Part 5, but this time we’ll have a completely new goal which is probably one of the most frequent ones that I’ve had when Modelling data for Power BI.

    In this, Part 6, we’ll go over the Full Outer Join from a purely practical standpoint.

  • Merge Operations in Power BI / Power Query – Part 5: Inner Join

    image Similar to the previous posts in this series, I highly encourage you to read the first 4 Parts ( 1 | 2| 3 | 4 ) that I’ve published so far around Merge / Joins inside of Power BI / Power Query.

    So far I’ve covered:

    • Left Outer Join
    • Right Outer Join
    • Left Anti Join
    • Right Anti Join

    In this, Part 5, we’ll go over the Inner Join from a purely practical standpoint.

  • Merge Operations in Power BI / Power Query – Part 4: Right Anti Join

    image

    Similar to the previous posts in this series, I highly encourage you to read the first 3 Parts ( 1 | 2 | 3 ) that I’ve published so far around Merge / Joins inside of Power BI / Power Query.

    So far I’ve covered:

    • Left Outer Join
    • Right Outer Join
    • Left Anti Join

    In this, Part 4, we’ll go over the Right Anti Join from a purely practical standpoint.

  • Merge Operations in Power BI / Power Query – Part 3: Left Anti Join


    If you haven’t read Part 1 and Part 2 of this series, I highly recommend that you read those prior to this post.

    In those previous posts, we went over the Left Outer Join and Right Outer Joins. At this point we got the basics on how Joins / Merge operations work inside Power Query / Power BI. It’s time to get even more clever with the usage of Merge Operations.

    In this, Part 3, we’ll go over the Left Anti Join from a purely practical standpoint.

  • Merge Operations in Power BI / Power Query – Part 2: Right Outer Join

    image

    If you haven’t read Part 1 of this series, I highly recommend that you read that prior to this post.

    In that previous post, we went over the Left Outer Join and some basics on how Joins / Merge operations work inside Power Query / Power BI where the position of the table (first one or second one), the columns being used of the join and my desired goal (aggregation vs expand operation) all have an impact on the whole Merge experience.

    In this , Part 2, we’ll go over the Right Outer Join from a purely practical standpoint.

  • Merge Operations in Power BI / Power Query – Part 1: Left Outer Join

    If you’ve used Power Query or Power BI before, you’ve probably seen the “Merge” button which displays a window like the following:

    image

    and this let’s you join 2 tables (or queries), and one of the questions that I get pretty often is: “What’s up with all of those ‘Join Kinds’ ? when should I choose which one?”

    and that’s why I’m writing this series of blog posts around this specific topic from a purely practical standpoint so you can get a glimpse of what each one of those merge operations can bring to the table.

    In this, Part 1, we’ll start with the default join which is the Left Outer Join.