Power BI

  • Combine or Append Data: Combining Flat Files

    0 Comments

    Combine Flat FIles from Folder

    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:

    A more complex scenario

    but what happens when you have multiple files? Let’s say 12 files. 1 for each month of the year.

  • Combine or Append Data in Power BI / Power Query: Main Concepts

    0 Comments

    image

    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.

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

    0 Comments

    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.

  • Relative Date Filters in Power BI / DAX / Power Query

    0 Comments

    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 […]

  • Fuzzy Matching in Power BI / Power Query

    1 Comment

    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!

  • Lazy Evaluation & Query Folding in Power BI / Power Query

    0 Comments

    image

    How do things work in the world? Humans are curious beings.

    We want to know how things work and perhaps harness them for our own purposes.

    In this case, we’re curious about how the Evaluation Model works for Power BI & Power Query when it comes to executing your queries and getting your data.

    You can read the official documentation from the Microsoft team here, but in this post I’ll cover the basics, so you can have a quick overview of how things work with Power Query / Power BI and how you can optimize your queries to better harness their engine.

  • Data Privacy and the Formula Firewall in Power BI / Power Query

    0 Comments

    image

    Have you ever received any Errors similar to these?

    image

    Formula.Firewall: Query ‘Merge1’ (step ‘Source’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

    image

    Formula.Firewall: Query ‘Query1’ (step ‘Added Custom’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

    This is the blog post where I’ll cover this specific topic to answer 3 questions:

    • WHY does it even exist
    • What triggers this error
    • How to better spot the cause of the error

    Before we start…let’s define one concept / feature:

  • Parameter Tables in Power BI / Power Query

    0 Comments

    image

    One of the most interesting and most unique scenarios is Parameter Tables in Power Query / Power BI.

    The way to make a dynamic Power Query solution is to make sure that it can be easily customizable and, at the same time, be able to work as more of a pattern rather than just a unique one off solution to a single file, table or whatever it is that your query works against. This is exactly where Parameters and Parameter Tables jump in.

    We’ve seen before how you can create Parameters using the Manage Parameters feature, but what if you want to have your own parameters coming from a Table from any other source?

    image

    The purpose of having Parameters this way is that it’s dynamic, on every refresh it’ll get the latest values, and it gives another end-user interaction layer, where people can input or change values of parameters completely outside of Power Query / Power BI perhaps on an Excel table, or perhaps on its own storage like a SQL Server database.

    In the end, it’s all about providing the user with the friendliest of experiences based on their specific requirements.

    In this blog post we’ll cover what Parameter Tables are in Power Query / Power BI and the types of Parameter Tables that you can use.

  • Parameters and Functions in Power BI / Power Query–Combine Files Experience

    0 Comments

    Combine Files

    I highly recommend that you read the first and second part in this series prior to reading this one, but you can jump right in if you understand the main concepts of parameters, arguments and functions in the M language.

    Understanding the Combine Files Experience: Practical Example

    Consolidation

    The scenario that we’re going to tackle is going to be THE most common way that people use Functions without even knowing what is going on.

    We’ll go over the new Combine Files experience that was introduced in Power BI Desktop at the start of 2017 and how everything changed from there.

  • Parameters and Functions in Power BI / Power Query–Custom Functions

    1 Comment

    image

    Power Query has over 600 native functions and the Power Query team keeps adding more and more.

    I wouldn’t recommend memorizing them, but you do need to understand the concept of parameters and arguments in order to understand what functions are.

    In this blog post I’ll go over what Custom Functions are and how you can create them. Be sure to check out Part 1 of this series before reading this one.

    M Functions

  • Parameters and Functions in Power BI / Power Query – Main Concepts

    3 Comments

    image

    Power Query or the Power BI Get Data Experience uses a functional language called M to perform its Data Preparation or Data Transformation processes.

    You can read this article to get to know more about Power Query and the M language, but in short, Power Query is the interface that assists you, through buttons and dialogs, to create the M code for you.

    Now, where would I see a function, an argument or a parameter in Power Query ?…and what are they? Let’s have a quick example to see them in real life.

  • The most important thing to learn when using Power BI

    2 Comments

    image

    You’ve started OR you’re in the middle of your Power BI journey and you’re confused as to where you should allocate your learning time and efforts

    Should it be DAX? Understanding the visualizations? the M language? Power Query? Power Pivot?

    SOOOO many keywords that appear when doing a simple online search, but WHAT is the core of everything inside of Power BI?

    This article covers my thoughts on where you should primarily focus your efforts when learning Power BI – let’s find out what is the HEART of Power BI.

    After spending more than 6 years using the toolset and going through various iterations and stages of what we now know as Power BI, these are my own thoughts and what I’ve found works best for the people that I’ve trained over the years.