Learn how to create a simple, but informative and interactive dashboard from daily updated data sourced from Johns Hopkins Institute.
Interact with the actual dashboard here.
Using Power Query, this type of work is extremely painless and quick. What’s more it refreshes data in just a click when more files are added.
While working with Power BI we often need to convert Seconds to Duration. This is easily done in Excel just by applying the formatting code “HH:MM:SS”. Unfortunately in Power Query or DAX, this is not possible. Here is the solution:
A brilliant new feature is now available in Power BI – Split column into rows. To understand why we need it, you must go and read the article – Analyzing badly captured Survey data or feedback forms. This method used Power Query concepts of Split and Unpivot. Now these have been combined into a single, intelligent command called Split columns into rows. It sounds confusing at first. But soon you will realize that it is an amazing tool. Learn it just 4 minutes.
Raw data looks like this
And you get a report like this. No need to use formulas or do any manual work.
You must have the May 2017 update for Power BI Desktop installed.
Here is a quick post to show the practical scenario when the Remove Top and Bottom Rows options are useful while cleaning up data using Power Query.
Often we get CSV files which have tabular data. Sometimes, there is a title, blank rows and some other related but unwanted data just above the useful tabular data. In such cases, we use the Remove Top Rows option.
If you get data from web pages (HTLM tables), the header row is repeated at the bottom. This option helps you remove the unwanted row.
Another common issue is that many reports dump data with a grand total at the bottom. Remove Bottom Rows option is also usefu in these cases.
It is quite possible that we focus on the top few rows while cleaning up data. It is equally important to import the data and look at the bottom rows. The bottom part of the data can have many issues which often go unnoticed and may even generate inaccurate results.