Simple Emails Tracker with MS Flow and Power BI

Many times we need to keep tracking emails related with a particular subject, received from a specific address or sent to a defined mailbox, etc. From day-to-day operations to external contacts, there are plenty of real life scenarios when it happens and there is a big probability be doing it currently.
The majority of the time our options are either, go email by email and copy the entries into a spreadsheet or pay for one of the thousand software off-the-shelf available in the market nowadays. If the latter can be expensive, the former is not only time-consuming but also it can lead to many errors.

The good news are there is a third option that is a really cheap and easy way to automate and improve this process. We are going to use Microsoft Flow, Outlook, SharePoint and Power BI from your Office 365 subscription.

Flow diagram

In this scenario we are tracking emails received in a Shared Mailbox with a particular Subject. In order to automate the process we are going to create a flow in Microsoft Flow that is recording and entry for each email received in a SharePoint List. Then we connect Power BI to the SharePoint List and import the data to create a Report that is going to be our Email Tracker. After published to the Power BI Service we are going to embed the report into a SharePoint Page. Parallel to it, an email with dashboard is sent daily to the subscribers. Continue reading “Simple Emails Tracker with MS Flow and Power BI”

Intro to Measures and Slicers

Check out Video #5 of Power BI Series and learn about DAX Measures and Slicers in Power BI Desktop.

This video covers:

  • How to create a Measure
  • Intro to Quick Measures and standard calculations
  • Intro to Slicers and Edit Interactions

If you are starting learning DAX I suggest:

  • Book:  Power Pivot and Power BI by Rob Collin & Avi Singh.  This was one of the first books I read about DAX.
  • Blog:  DAX Fridays  by Curbal.com . Ruth releases a video every Friday covering DAX functions (and more) and to date, the playlist counts with more than 80 videos.  Awesome work .

There are plenty of other sources (books, blogs, videos, etc), but these are my recommendations if you are new to DAX and Power BI and want to learn the basics.

DAX and M edition Helpers

We need to admit, the GUI for Power BI Formula Bar (DAX) and the Power Query Advanced Editor (M) are not the most user-friendly.
DAX editor (formula bar) is getting better but still quite limited. M editor (Advanced Editor) … OK, this might be something more advanced and eventually not everyone will need to write M functions.

But just in case if you are interested on it, these are my Helpers to write and format DAX and M code:

    • DAX STUDIO – “DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular.” by SQLBI
    • DAX FORMATTER -“DAX Formatter is a free tool that transform your raw DAX formulas into clean, beautiful and readable code.” by SQLBI
    • POWER BI HELPER – “Export the Entire M Power Query Script from a Power BI File” by RADCAD
    • NOTEPAD++ – “Notepad++ is a text editor and source code editor for use with Microsoft Windows.” by Don Ho

Continue reading “DAX and M edition Helpers”

Calculated Columns in Power BI Desktop

Check out Video #4 of Power BI Series and learn two easy ways how to create a calculated column in Power BI Desktop.

This video covers:

  • Create a Calculated Column in DAX
  • Create a Calculated Column in Power Query
  • Delete Calculated Columns differences and Errors

You can download the files here:
Link to GitHub

And access the online dashboard here

Dublin Data & BI Summit

From the 24th to the 26th of April I am going to attend the Data & BI Summit in Dublin.

This is the Microsoft Power BI big event of the year. During three days Power BI authorities, gurus, wizards and evangelists will gathering under the same place. The result can’t be no other than awesomeness!

If you are around drop me a message and let’s have a coffee.

 

Load Multiple files and Append Query

In the 3rd video of the Power BI Series I show you how to load multiple files into Power BI using the Query Editor and to troubleshooting a Date type Error.

In this video we covered:

– How to load multiple excel files to Power BI
– How to use  Append Query to create a unique dataset
– Date error- Use locale
– Regional Settings

Keep tuned for the next video and learn How to Create Measures and Calculated Columns 

You can download the files here:

  1. Link to  GitHub
  2. Link to  OneDrive

And access the online dashboard here

Power BI Series – Refresh Data

In the 2nd Video of the Power BI Series I show you how to refresh the model with new data.

In this video we are covering:

    • Refresh the model with new data
    • Introduction to Power Query
    • Common Errors and how to troubleshoot it
    • Best Practice for file naming

Keep tuned for the next video and learn How to append multiple files using  the Power Query editor

You can download the files here:

  1. Link to GitHub: https://github.com/Bordalos/Power-BI/
  2. Link to OneDrive: https://1drv.ms/u/s!At1CSmc…

And access the online dashboard here

Power BI Series

Check out the 1st Video of the Power BI Series and learn how to build your first Power BI report in less than 15 minutes.

In this video we are covering:

  • Why should you move away from reports in Excel
  • How to install Power BI Desktop
  • How to connect to an excel dataset
  • Introduction to  Card, Multi-Row Card, Bing Map, Bar Chart, Line Chart, Matrix, Donut Chart visuals
  • Set Conditional Formatting Data Bars
  • Size, Align and Distribute visuals
  • Create a Top and Bottom N KPI
  • Edit Interactions for cross filters

Keep tuned for the next video and learn how to refresh the report with new data

You can download the files here:

  1. Link to GitHub: https://github.com/Bordalos/Power-BI/
  2. Link to OneDrive: https://1drv.ms/u/s!At1CSmc…

And access the online dashboard here

My Stock Investment

Yes this is my other “hobby”.

I’ve built this report to track the performance of my investment in stocks.
As you may suspect it is something that I particularly care and for that reason this report is a real world scenario!
Due to privacy concerns I am only showing % weights and variations on the investments. But in truth, that’s the most important outcome of the analysis. Just assume x dollars and do the mats to convert it to  dollar amount

Technically, I’ve used a few distinct approaches to collect and transform the data for the report:

  1. Excel file located in OneDrive for manual input of my transactions;
  2. R Script to scrap daily prices online from Google (Quantmod package);
  3. HTML direct query Yahoo finance;
  4. Image URL method to display the companies logos (OneDrive repository);
  5. Parameter and Invoked Function to change the Proxy and automatically recalculate the figures;
  6. A bunch of measures with var … return if …, lookup table and others not so fancy but effective technics.

This still a work-in-process and I am planning to do a good piece of improvements. So not be surprise if it had changed meanwhile.
If you are interested to know anything related to this report and the technics I’ve used here,  just drop me an email or leave a comment below.

How to install Power BI

Installing Power BI Desktop is really simple and it’s FREE . Just go to https://powerbi.microsoft.com/en-us/get-started and download the latest version to your machine. Run the executable and boom 2. You are now empowered to create your own amazing reports.

The PBI desktop will be from now on your best companion and soon you won’t be using any other toll to do your analysis.

In more than 95% of the times you will already have a MS Office license in your Organization. Although if that’s not the case and you don’t have one licence you can sign up Office 365 for a 1 month trial.

Note: You will need to have a work or school email account to sign in into the Power BI Service or Power BI Mobile. More on that later. For now I suggest you start to discover the possibilities of the desktop version.