Tag Archives: power bi

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

  • Query Error Auditing in Power Query for Excel and Power BI

    image

     

    As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.

  • Data Types, Data Conversion and Ascribed Data Types in Power Query and Power BI

    image

    As we saw in a previous post, there are many things that we need to consider when dealing with Excel files since even the extension of an Excel file can dramatically impact your solution and how Power Query interprets the contents of that file.

    In a more broader sense, we also need to take in consideration 3 key elements that apply to every single data that lands inside Power Query :

    • Data Types – every field/column can have a specific Data Type associated to it
      • Data Type Conversion – every field/column can have its members (rows) converted to another Data Type as well as the Data Type of its field/column defined to a different Data Type.
      • Ascribing Data Types – instead of performing a conversion for every member of a field/column, we can simply define that the column should be considered of a specific Data Type without doing any conversion. This is considered ascribing a data type.

    You might’ve heard about Data Type Conversion, as it’s what Power Query automatically does when you click on the “Data Type” dropdown and select a data type for a column, but you can also do what it’s called Ascribing a Data Type, where you can define a new Data Type to a specific field/column without doing a conversion process. This post will showcase how to do this and what are the benefits of doing this.

  • Calculate Days between dates using Power Query / Power BI

    image

    Have you ever wanted to find out how many days you have between 2 dates? perhaps how many Mondays? how many Sundays? perhaps Saturdays and Sundays?

    Well, in this blog post I’ll show you how you can do that with Power Query inside of Excel and/or Power BI and how you can extend this to other scenarios.

  • Recurring dates with offset in Power Query


    Imagine this scenario, you are a Doctor who has multiple appointments with patients on a daily basis. Sometimes, you need to schedule follow up appointments with your patients just to make sure that everything is going according to plan and basically do a check up.

    You record these appointments in a simple spreadsheet like the following:

    Initial
    Appointment
    Patient
    Name
    Follow
    up Appointments Needed
    Frequency
    (every x days)
    11-Jan-18 Audie Livengood 3 14
    28-Oct-17 Curt Gatz 2 7

    Where, for example, the first row is telling me that the patient under the name of ‘Audie Livengood’ had a initial appointment on January 11th of 2018 and needs follow up appointments every 14 days, for a total of 3 follow up appointments needed.

    You want to calculate how your agenda might look like based on the appointments that you’re making and how many follow up appointments you’re needing on average per patient, so you want to use Power Query to calculate a table like the following:              

    Initial
    Appointment
    Patient
    Name
    Follow
    Up Appointments Dates
    1/11/2018 Audie Livengood 1/25/2018
    1/11/2018 Audie Livengood 2/8/2018
    1/11/2018 Audie Livengood 2/22/2018
    1/11/2018 Audie Livengood 3/8/2018
    10/28/2017 Curt Gatz 11/4/2017
    10/28/2017 Curt Gatz 11/11/2017

    Can you do this with Power Query? Yes! you can

  • Power BI / Power Query and Excel files–XLSX vs XLS formats and datatypes

    image

    This is a topic that has been previously covered here by my good friend Ken Puls, but it was just recently that I found this response from Ehren about how Power Query interprets xls files differently to xlsx files and it is certainly worth a blog post to cover this in more detail.

    I highly encourage you to read this post if you use Power Query inside Excel or Power BI against Excel files either in *.xls or *.xlsx

  • Announcements: Power BI Red Carpet Program + New Power Query book is out!

    image

    Power BI now in Panama!

    Powered Solutions is now part of the Power BI Red Carpet Program

    The Power BI Red Carpet Program is an invitation-only Microsoft Program for a few selected partners worldwide and Powered Solutions has now been nominated, selected and activated as the very first and only Microsoft partner to join this program in Panama. The program helps partners by providing the tools, training, content, and support in order to deliver a successful Power BI based solution and bring data to life.

    This program will enable us to continue delivering the Top Quality service that we provide around Microsoft Business Analytics Technology and to be even add more value to the value-proposition that our solutions can have for our customers.

    If you’re looking for a Microsoft Partner that will help you bring your data to life in Power BI, then contact us at info@poweredsolutions.co for a meeting

    The Book is ready!

    Long overdue! but the book is now available

    The book is finally ready and you can get your copy here. This book follows the same learning path that Ken and I (Miguel) created for the Power Query Workshop, so you can expect some basic to advanced Power Query – there’s something for everyone!

    S2A English Final

    Don’t forget to download the supporting files for FREE! Click here to download all of them

    In case you want to take your Power Query experience to the next level, then I’d recommend you sign up for our last Power Query workshop of the year on the 24th of November. Click here for details

  • New Power BI Desktop (fka Power BI Designer)

    image

    (A re-engineered tool with a new Visualizations engine)

    Power BI Desktop is the tool that replaces the Power BI Designer –  in short, it’s the new and optimized version of the Power BI Designer that comes with new features, new integrations and even a completely new take when it comes to the Microsoft policies as they are going open source with their Visualization engine.

    Here’s a quick blog post of all the new things that you’ll encounter in this new tool.

  • Power Query for Excel – Replace values using values from another column

    Another video trick for Power Query. A glimpse of how advanced you can go with Power Query using custom M code.

    Be sure to check out more tips and tricks like this one on the oficial Power Query Training page

  • What is Power BI? 2015

    This information is based on the features and services that Power BI offers as of January 2015.

    Microsoft Power BI

    • Excel Add-ins

    • Cloud Service

    • REST API

    What is Power BI?