Tag Archives: excel

  • Combine or Append Data: Combining Excel Files

    image

    In the previous post we saw how we were able to combine multiple files from a Folder.

    In that post we were using flat files but, how would that process be for Excel files?

    This is the post where we’re going to see the difference between simple flat files and more complex files (like an Excel workbook) when it comes to using the Combine Files experience inside of Power BI / Power Query.

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

    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.

  • 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