Tag Archives: excel

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

    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

    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

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

  • 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

  • New Demo site for Powerpivot 2013

    image

    Recently, Portalfront hosting has released a new test site using cutting edge technology such as Powerpivot, Power View and Sharepoint 2013.

    Ever wanted to see how Powerpivot or Excel works on the Web?

    Take a look at the reports that are available on the site and if you are interested in a free test site, click the link below!

    Check out the Demo Site

    Request a Free Test Site!

  • Data Explorer & Power View – Using Twitter Data (JSON Format)

    Every once in a while… Microsoft releases extremely cool tools to the Excel audience that leaves us with a face similar to this one –> Surprised smile and they just recently introduced the latest version of Data Explorer

    (which caused this to many people –> Surprised smileSurprised smileSurprised smileSurprised smile) .

    Within the past 3 years, Microsoft has released

    • Powerpivot
    • Power View
    • Data Explorer
    • GeoFlow
    • Excel 2013 (all the new functions and features apart from the ones above)

    All of them that answer to the needs of business users to get their hands on the Dataa and be able to explore it and reach extremely useful insights…all of this thanks to the Self-service BI concept.

    Data Explorer answers the need of the user that needs to transform his data in an easy and automated way, translating this into excel-geeky language, it means:

    • No more macro recordings
    • No more helper columns
    • No more complicated formulas and arrays that made Excel Crash

    Data Explorer it’s a tool that enables you to:

    1. Extract
    2. Transform and
    3. Publish

    your data. However you want. Check out this post from The Data Specialist:

    http://thedataspecialist.wordpress.com/2013/03/05/microsoft-data-explorera-review/

    to know more about what’s Data Explorer

  • Someone answered the million-dollar question! (What is Powerpivot?)

    YES! IT’S CORRECT!

    Someone answered the million-dollar question and I feel like Steve Harvey or some other host about to give the million-dollar check to the winner.

    My friends at Portalfronthosting created this AMAZING video with subtitles in english and spanish! Sí, en español

    Check it out and don’t forget to share it! The revolution has just begun

  • Current and upcoming youtube videos

    This will be a short post since I’m trying to get back to record some youtube videos. If you haven’t seen any of my videos you can go here

    http://www.youtube.com/poweruserxl

    to see one of the most viewed videos in my channel but this post is primarily to get YOU, the reader, a suggestion on what I should be doing next or what you want to see on that channel.

    Hope you like the videos and leave some comments with your opinions or suggestions!

  • The Powerpivot Revolution (the new perception of the Excel user)

    zero-to-hero - The XL Pro revolution(not really from zero, but you get the idea of how it changes the perception of an excel user)

    Powerpivot has been called the best feature to hit Excel in 20 years by many MVP’s, developers, BI experts and excel users like myself. You’ll probably be asking yourself why is this the best feature of Excel thus far?

    Well, it’s simple…