Miguel Escobar

Author Archives

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

  • Navigation Window / Folder in Power Query and Power BI

    Motivated by one of the latest videos from the MSFT guys behind Guy in a cube:

    where they talk about one of the hidden secrets behind Power Query / Power BI, I wanted to talk about another one of those hidden gems inside Power Query / Power BI that is not completely visible or intuitive for most folks when they start using the tool.

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

  • New Web Scraping experience in Power BI / Power Query (Using CSS Selectors)

    The latest version of Power BI Desktop came out with a new Web Connector specifically designed for Web Scraping Scenarios. in this blog post I’ll try to go deep into how this new experience works and how you can take advantage of it.

    Before we move forward, you’re gonna need the latest version of Power BI Desktop (May 2018 for me) and also enable the Preview feature in the Options window:

    image

    My Scenario: Get Data From Amazon

    I want to find out how many books are out there that have anything to do with Power Query. I want the reviews that go with them, the name of the authors, when they were released and, of course, the names of these books.

    The best place to find this information is probably Amazon. So I went on Amazon and did a quick search using the keywords “power query”.

    image

  • First Impression on Power BI Incremental Refresh

    fireworks

    Power BI just recently released the ability to set up Incremental refresh policies through the Power BI Desktop.

    This is a short blog post with my first thoughts on it.

    Everybody has been excited about the possibility of doing Incremental Refresh through Power BI. Back in the day, this was only something that you could accomplish using Partitions in SSAS, which would require a server and it didn’t use M syntax at all.

    Recently there have been new releases like Azure Analysis Services and new versions of Analysis Services that have Power Query integrated into it which allow for really dynamic M syntax. To this point, everything was through a SSAS but now we have the ability to basically create partitions through a Power BI Desktop model.

    I not only wanted to test this out by itself, but by combining it with my Custom Connectors. Could I create a scenario where I’m getting data from the WooCommerce API (using my Custom Connector from here) and set up an incremental refresh?

    Let’s find out.

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

    image

    I’ve been working with Power Query inside of Excel and Power BI for the past few years, but I’ve always tried to stay on top of its competitors, trying to make sure that I’m investing my time using the best tool that there is.

    This is one of those times where I allocate some time to find out if Power Query is still the best ETL tool for the Business Analyst out there.

    In this case I’m going to compare 3 tools:

    • Power Query inside of Power BI Desktop and representing Microsoft
    • Tableau Prep pka as Project Maestro ad representing Tableau
    • Trifacta Wrangler – representing both Trifacta and Google’s Data Prep (since for all intends and purposes, it provides the same UX for the end user)

    Disclaimer: I am in no way being sponsored nor promoted by any of these companies. I’m not a Microsoft MVP nor a Tableau Zen Master. My main goal with this comparison is to find the best ETL tool out there and use it.

  • Refreshing a Power BI Custom Connector in the Cloud

    image

    These are GREAT news! Finally we have a way to refresh data from custom connectors on the Power BI service.

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

  • The Navigation Step in Power Query and Power BI – Navigating to rows, columns and cells

    image

    What is a “Navigation” step in Power Query?

    GIF Navigation

    When using Power Query, you might’ve come across a step that reads “Navigation” and is usually automatically created for you by Power Query, but…what does it do?

    Well, if we look at the code that it was created for the Navigation step, it reads:

    = Source{[Item=”SalesTable”,Kind=”Table”]}[Data]

    It has some curly brackets mixed with some square brackets and some nomenclature that seems pretty strange at first. This is one of the ways that Power Query automatically creates a navigation step, but there are other ways to achieve the same result and depending on the situation Power Query might create a different code.

    This is the main reason why I’m writing this post. To document what are the ways that Power Query has in order to Navigate to a specific Column, Row or even a specific Cell.