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.

Step 1 – Define Email Rules

In order to be possible to automate the process is critical to have set of unique rules that are what define which email do we want to track. It can be based on a subject that contains a specific text, emails received from a sender or a group of senders, emails sent to an address, etc.
In this scenario we defined to track emails received in the Inbox of mailbox “info@bordalos.com” that contains the text “REF:” in the Subject.

Step 2 –SharePoint List

For this example we are going to create a List on a SharePoint Team Site.

To simplify we define four main columns: Title, ProjectID, Date and Comment.

See more about how to create a list in SharePoint here

! Tip: alternatively, you can use Excel to collect the entries.

Step 3 – Create the Flow

Once clearly defined the email rules, we are going to create automated workflow with Microsoft Flow.

The first thing we must do is to create what will trigger the workflow.
In our case, the Trigger is when a new email arrives in the Inbox folder of info@bordalos.com that contain “REF:” in the Subject.

After the trigger, we need define the running actions. The first step is to get the email.

One extra step is to transform the email body content from HTML to Text. This is necessary in order to be able to collect details that are included in the email and that will be important for our tracker.

Now we instruct to create an item in the SharePoint List created in Step 2. Parallel the email is marked as read.

! Tip: You should always perform some testing before move on to the next steps. It can avoid future errors due to rules not considered at the beginning or the Column date format on the List. There are three option to test the flow.

Select accordingly and Confirm the entries are recorded in the list as expected.

Step 4 – Power BI Report

At this point we have our data source created. Simply connect Power BI Desktop (or Data-flow) to the SharePoint List. Now is the normal process of report creation in Power BI: Edit -> Model -> Visualize -> Publish.

Step 5 – Refresh data, Set Alerts, Subscribe email

As we are looking for a fully automate process we are going to schedule data to refresh once a day together with an email push to subscribers. You can also set any alert based in a KPI.

Step 6 – Embed on Report on SharePoint Team Page

A good method to share your tracker with your team is to embed it on a Team Site on SharePoint.

In summary, we have easily automated a way to track the emails received without any manual intervention.
There are plenty of Microsoft Flow templates, with Low or No-Code required, that can be used to streamline daily repetitive tasks.

Microsoft Certification: Exam 70-778 and Exam 70-779

Many people have asked me how I prepared to take Microsoft Exam 70-778 (Power BI) and Exam 70-779 (Excel).

So, this is my advice based in my experience:

You must be already familiarized with DAX, Power Query, T-SQL and SSAS. Doesn’t mean you cannot learn it and pass the exam but without some previous knowledge about these topics it will surely take longer, and you are increasing the probabilities of be unsuccessful.

If you are only starting now working with Power BI, better to consolidate the basics before taking the exam. There are plenty of invaluable resources in the web where you can learn, for free! From Microsoft Documentation to gurus’ blogs such as CurbalPowerPivot Pro, RADACAD, SQLBI, Guy in a Cube… and many, many others.

In my case I had a solid 2+ years learning Power BI in work environment when I decided to sit for the exams. Of course, it doesn’t mean you need all this time, but I think it is important to notice it.

The first book I read was Power Pivot and Power BI – by Rob Collie & Avi Singh and I honestly recommend it.

Then I did the Microsoft courses Analyzing and Visualizing Data with Power BI and Analyzing and Visualizing Data with Excel in EDX which are also the online training recommended by  Microsoft in the Exams page.
Note you can just Audit the curses, i.e., you don’t need to pay for them unless you are pursuing the certification. In my case yes I paid but was because these were part of the Microsoft Professional Certification in Data Science I was taking at the time.

When I decided sitting for the exams (Summer ’18), I bought the official exam reference books Exam Ref 70-778 Analyzing and Visualizing Data by Using Microsoft Power BI  by  Daniil Maslyuk and Exam Ref 70-779 Analyzing and Visualizing Data by Using Microsoft Excel by Chris Sorensen.

The two Exam Ref are well organized, and one can see Authors have put a lot of effort to create a study path. Chapters follow the Exam structure and reference many extra resources. Kudos to them. Important, I studied for one exam at the time, 70-778 first and then 70-779. There are so many similarities between Power BI and Excel that can trick you that I won’t recommend to study for both exams at same time.

A third important resource I used was the white paper recently published at the time: Planning a Power BI Enterprise Deployment by Melissa Coates and Chris Webb.
This paper has been extremely useful for me until today and still one of my main references to a complete view of Power BI Environment.

As Practice Questions, I summarized and reviewed the questions of the Microsoft Curses, the Exam Ref books and I did the free test provided by Accelerated Ideas.

Following the practice examples in the Exam Ref books is also recommended and a good way to train.

The exams length time is between 120 and 150 minutes but you must read carefully the case studies and the questions, hence is important to manage tightly the time. There are questions that all answers are right, and you most select the most complete. Be aware of these.

And finally, be calm! If you get stuck move for the next question. You can come back to it later.

I hope it would help you and good luck for the exams!

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.

 

Stop wasting money and time

How much time do you spend doing repetitive and tedious tasks to get your data and create your reports?

Multiply it by the cost per hour and you will find how much money you are spending on it.
Now eliminate this wasted time and instead, use it to grow your business or add value to your organization.
How much value are you adding?
Sum both amounts and you’ll find how much money your losing by missing out on the process automation.

Don’t be fooled by small tasks. Say for example that weekly report that takes you 2 hours every Monday to run. You spend roughly 8 hours per month or 112 hours year.
If the cost per hour is €20 you spend €2240 doing this task. Does it worth €2k? And how about the business you can do with this 100+ hours? I am sure you got me.

At this point you’re asking: How can I have the task done not loosing all that amount of time and money?
My answer is: Eliminate, Simplify, Automate, Delegate.

“Never automate something that can be eliminated, and never delegate something that can be automated or streamlined.” Tim Ferriss

Eliminate – You would be surprised how many times I heard “I am doing it because…
this is the way it’s always been done ”
So, do you really need to do that report? Does anyone look at it in the end?
If not, stop doing it.

Simplify – Over time we all tend to overcomplicate. Is just one more detail, one more chart, one more process… and we end up with something that you’ll need a guide to understand what it is. Often less is more. Focus in what is really important and remove all the unnecessary noise.

Automate – Our most precious attribute is we are thinking beings. Thinking is what distinct us from other animals and what make us continue to evolve. No one should have to do tedious and repetitive tasks that can be done by a machine, a system or a process.
Leave the dirty work for the machines and concentrate in doing what you are good at…Thinking.

Delegate – The breakpoint is whenever you can earn more than what costs you to delegate, you should look for someone to do it for you.
Delegate it’s hard mostly because we are afraid of losing control of something we see as ours. But it is a necessary pain if you want to grow your business or get more time for what you really enjoy.
Find someone with the right skills to do that task for you and free yourself. At the end you are still profiting in money and time.

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