Tag Archives: power bi

  • 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.

  • Comparing Similar Products (Movies) with Power BI

    Back Story: Back in the day, I used to work for this company with a truly all-star / amazing team. We were all part of this company called 20Th Century Fox, and we were in charge of the Theatrical distribution of films in Central America.

    Business Intelligence wasn’t a standard in the industry – at least not how we know BI nowadays, and I was the main person leading the charge of using BI tools to analyze the sales aspect of the business – specifically the Box Office. And that’s how I found PowerPivot (without the spaces back then) and the DAX language. I went deep into it and even took the online Power Pivot Workshop that Marco & Alberto used to deliver back in December of 2011 .

    I was able to implement a WHOLE bunch of reports, analysis and dashboards thanks to Power Pivot, but one thing that I couldn’t implement back then (because I started the journey with my own company), was a Comparison of “Similar” movies for the release planning of an upcoming movie.

    See…before a film gets to a certain theater / cinema / studio / location, it has to go through a certain planning phase. It starts with an estimate on how much that movie might be able to make, but….how can we tell?

    Well, you had a few options, but in the end it was all a manual approach where you’d need to think about what movies might be similar to this one (from every single perspective that you can imagine). Then you’d go through the system and search for every single similar movie one by one so you can grab what was the total gross revenue for each.

    What if…you had a table for all the similar movies (usually called Comps) and you could simply select the Movie that you’re interested in and it’ll display all of the Comps?

    Of course, this doesn’t only apply to movies, but to any product / service that you’d like to compare against similar products.

    This is where this blog post comes in!

  • Clever Filters in Power BI / Power Query with Merge Operations

    Power BI Clever Filters

    Have you ever wanted to do a specific type of filter that might be too specific to do with just the regular filter drop down or even the advanced filter operations in Excel?

    Let me give you an example of one of those scenarios. Imagine that you have a table like the following:

  • Extracting Header and Footer Values from Documents, Files using Power BI + Using PDF Connector

    image

    Have you ever had a similar situation where a REALLY important value of the file is in either a header or a footer section? Let me give you a clear example with the following file:

    SNAGHTML18603940

    In the image above you’ll see that we have 2 values in the header section right under the logo of my company. Now, this is a PDF file, so we’ll connect to that PDF file, get the data underneath those header values (which is basically a table) and then create a new column specifically for the value of the Sales Group. You can imagine that I have a bunch of PDF files in a folder and each of those PDF files is for a specific Sales Group, so I need to have that value as a new column.

  • Improved Web Scraping Experience in Power BI

    A lot of things have happened in the last couple of days. I’m for the first time in my life in Seattle spending some quality time with people that I’ve never met in my life other than through the power of the internet. It’s a nice feeling to finally put a face to a name that you always see on your inbox.

    I’ll be speaking later today, but I had to get this post out to the world as soon as possible.

    I’ve covered the new Web Scraping experience before in here, and talking with the product team today at the Business Applications Summit they (Ehren specifically) told me that they just updated and improved the experience, so he showed me a demo and I took home my homework so I could implement what he showed me on my own dataset

    What’s new

  • RETRY recursive function in Power BI & Power Query

    Recursive functions are a topic that I rarely come across.

    To give you some perspective, the instances that I’ve needed recursive functions can be counted with just 1 hand.

    This is one of those situations where a recursive function is probably the best way to go.

    Big thanks to NicoPer who posted this question on the Official Microsoft Power Query forum.

  • Results: The Best ETL tool for the Business Analyst–Tableau Prep vs Power BI vs Trifacta Wrangler (Google Prep)

    The results are in! Let’s find out which tool is better for the average Business Analyst based on the 3 scenarios that I showcased a few weeks ago here.

    I was able to get in touch with the Product Team from Microsoft’s Power Query and also the one from Trifacta’s Wrangler. I tried getting in touch with the Tableau folks, but they  never replied to my emails. Luckily, someone within Tableau’s Sales team did see my videos on YouTube and was able to give me a more refined view of what Tableau is capable of. Big thanks to everyone who helped me along the way through forum responses and multiple emails!

    We’re going to break this down by each scenario and then we’ll tally up the results to figure out who’s the winner.

  • Reconfiguring a Step in Power Query for Excel and Power BI

    SNAGHTML584a8a

    One of the coolest features of Power Query it’s something that we colloquially call ‘the gear icon’ which is an icon that appears to the right of some steps. Not all steps, but to most of the steps that are automatically created when you click on a button of the Power Query UI.