The Story of my Book, “Expert Data Modeling with Power BI”

Expert Data Modeling with Power BI
Expert Data Modeling with Power BI

In 2020, the world celebrated the new year with many uncertainties. Well, life is full of uncertainties, but, this one was very different. The world was facing a new pandemic that never experienced before. The first COVID19 case in New Zealand was confirmed in February 2020. In March 2020 the entire country went to lockdown for the first time. The world was experiencing a massive threat changing everyone’s lives. I was no different. Every day was starting with bad news. A relative passed away; a friend got the virus; the customers put the projects on hold etc. Nothing was looking normal anymore. You can’t even go to get a proper haircut, because everyone is in lockdown. This is me trying to smile after getting a homemade haircut. I bet many of you have done the same thing.

Soheil's Homemade Haircut
Soheil’s Homemade Haircut

One day, I checked my email and saw a message from Packt Publishing. They wanted to see if I am interested in writing a book about Power BI. That was a piece of good news after a long time. I always wanted to write a book about Power BI. Indeed, I attempted for the first time in 2016, but I couldn’t manage to get my ducks in a row to grasp the publishers’ attention.

I was not unfamiliar with writing books; indeed, I wrote my first book back in 2006 about Multimedia Applications in Persian. One of my passions in life is listening to music. And CDs were the most accessible music source with high-quality sound. I recall I saved money for some months, and I bought a Discman to listen to the music on the go. But CDs are rather bulky, and you could not have many of them in your pocket. So the next project was to save even more money to buy an MP3 player. But, converting Audio CDs to MP3 without compromising a lot on the sound quality was a real challenge for many people. And, that was my motive to write my first book in Persian to share my little knowledge with everyone. 

Continue reading “The Story of my Book, “Expert Data Modeling with Power BI””

Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

It’s been a while that I use Microsoft To Do to organise my daily tasks. From work-related tasks to buy groceries. While Microsoft To Do is super easy to use but there are some challenges in using it more efficiently, especially when you have multiple O365 accounts within different organisations. Here are some of the challenges I faced; you may face other challenges too:

  • The Microsoft To Do app for Windows devices is very user friendly with amazingly good features like the ability to add multiple To Do accounts. However, we currently have to select which account we would like to use and the app shows all our tasks within that specific account. This means we can not see all our tasks from all our accounts in a single place.
  • The Microsoft To Do app for iOS devices is also very handy to use, but it lacks adding multiple accounts. Hence we cannot see all our tasks from multiple O365 accounts on the app. 🙁
  • We can use the Tasks within the Microsoft Outlook desktop application (I used the Windows version) which is by far the most comprehensive one with tons of features. While we can see tasks from multiple accounts in a single place, it is a real challenge if I want to know which task is assigned to which account. Besides, it is really hard to answer some questions like, how many high-priority tasks I have for today or the week ahead. I know, we can group tasks, but, it is still not so intuitive.

For the above reasons, I searched for a product that can do all the above at once. After spending some hours, I thought, well, I have to do it myself.

With that, let’s go ahead and see how we can get the job done in Power BI.

Note:

This method is not working for Microsoft To Do using personal accounts such as Outlook, Hotmail or MSN. If anyone knows how to add those, please let us know in the comments section below this post.

This is a long post that took me a reasonable amount of time to write. So I added the following table of contents so you can quickly jump to a subject of your interest.

Table of Contents

How It Works

Microsoft Power BI is NOT a reporting tool only. We can connect to many data sources, mix and match the data, create data models and visualise the data. So it should be possible to connect to multiple To Do accounts, append the data, create a simple data model on top of that, and visualise the data to answer our questions or our customers’ questions. The Microsoft To Do data is accessible via the Microsoft Exchange Online connector available in Power BI. The rest depends on our requirements and what questions we would like to answer.

In my case, in which I am the end-user of the report, I would like to be able to know:

  • Today’s tasks: All tasks that their StartDate or DueDate is today or the Tasks without any StartDate and DueDate
    • Number of tasks
    • Number of important tasks
    • Tasks by mailbox
    • Tasks details
      • Task list
      • Task description
      • Status
      • Start date
      • Due date
      • A link to the task itself that I can update if I want to
  • All Tasks
    • All above plus
      • Number of open tasks
      • Number of completed tasks

You or your customer(s) might have different requirements, but once you understand how to get the To Do data from Microsoft Exchange Online and do some data explorations to find out what you are after, you’ll be good.

Continue reading “Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI”

Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.

Method 1: Getting the Excel File Path from the Excel Desktop App

This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.

In SharePoint Online go to the desired document library then follow these steps to make it work:

  1. Select the Excel file
  2. Click the Open button
  3. Click Open in app
Opening Excel file from SharePoint Online in Excel Desktop App
Opening Excel file from SharePoint Online in Excel Desktop App

This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:

  1. Click the File menu
  2. Click Info
  3. Click the Copy path button
Copying Excel Path from Excel Desktop App
Copying Excel Path from Excel Desktop App

So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.

Open Power BI Desktop and follow these steps:

  1. Click Get data
  2. Click Web
Getting data from Excel stored in SharePoint Online in Power BI Desktop
Getting data from Excel stored in SharePoint Online in Power BI Desktop
  1. Paste the path we copied from Excel in the URL text box
  2. Delete the ?web=1 from the end of the copied path
  3. Click OK
Modifying the path copied from Excel to get the data in Power BI Desktop
Modifying the path copied from Excel to get the data in Power BI Desktop
Continue reading “Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop”

Exporting Power BI Desktop Data Using Power BI Exporter

Exporting Power BI Desktop Data Using Power BI Exporter
Exporting Power BI Desktop Data Using Power BI Exporter

I am excited to announce that our amazing team at Data Vizioner has developed a lightweight and easy to use external tool for Power BI Desktop named Power BI Exporter. The Power BI Exporter is a free community tool available for download here. With External Tools General Availability, you can expect to see more and more External Tools built by our amazing community members. I personally stunned to see many amazing ideas turn into useful tools. Learn more about Power BI announcements about External Tools GA here. I am humbled to be named in the announcement though. So thank you to Microsoft for that.

Let me start with a little background. In the past few years, I wrote a series of blog posts about exporting data from Power BI Desktop and Power BI Service. Those posts are quite popular. They are in the top 10 most visited posts on my website, contributing to about 15% of my website visitors. Here are the previous posts I wrote on this specific topic:

Apart from my website statistics showing many people want to export data from Power BI Desktop, some of my customers asked the same question. They wanted to export the curated data from their data model within Power BI Desktop to CSV format and make the curated data available for their other platforms. While all the methods mentioned in my previous blog posts are working, some users still find them complex. So I thought, we can make it better. We can make a straightforward tool that exports the data with only two clicks. So we started building the Power BI Exporter as a micro-project. We added some more ideas to the original idea of only exporting the data. We thought it is good to export the data along with the table names, column names and relationships. Having that information handy, we can quickly build the same data model as the one we exported its data but using the CSV files as the data sources. The other idea was to pack everything in a ZIP file on the fly, so we have a single ZIP file, including the tables, columns, and relationships. As a result, the first version of the Power BI Exporter is born. In this post I explain how it works.

Downloading and Installing Power BI Exported

You can download Power BI Exporter from its official webpage from Data Vizioner website. You require to enter your email address then click the Download button as shown in the following image:

Downloading Power BI Exporter from Data Vizioner Website
Downloading Power BI Exporter from Data Vizioner Website
Continue reading “Exporting Power BI Desktop Data Using Power BI Exporter”