We continue exploring data refinements using Power BI. In these videos we cover Grouping on dates, numbers and text along with creating hierarchies.
We have already learnt how to import and clean data from various sources using Power Query (Transform). Once the data is inside Power BI Data tab (or Excel Data Model of Power Pivot), we can refine and enrich it further using various features like synonyms, custom sorting order, formatting, summarization and more. Watch the video for details – English as well as हिंदी versions are available.
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.
This is a part of data clean up series based upon the 10 rules for clean data. This video covers rules 1, 5, 6, 10 – cross-tabs, data growing horizontally and multi-layered headings.
Starting a new video series on Power BI. Here is the first video: Power BI Data Import.
We spend lot of time in data cleaning. But there is no simple definition of exactly what clean data means. Therefore, I created a simple 10 item checklist. If all 10 items are correct, data is clean. Simple and easy to use.
10 rules for Clean data
Remember, here we are talking about input data – not output (reports).
- Each column must have a heading
- No blank headings
- No duplicate headings
- No formulas in headings
- No merged cells
- Each column must have ONE meaning
- Each column must have ONE type of data
- No subtotals or grand totals
- Formatting should not be used instead of data
- Data must grow vertically, not horizontally
Convert clean data to a Table
If you are using Excel, as soon as data is cleaned, convert it into a Table. Why? Here are the benefits of using Excel Tables – 13 articles.
I have noticed that most customers are reluctant to pay the 9$ license cost for Power BI. Here is how you can use Power BI free of cost. I am a strong proponent of purchasing licensed software. This articles is not meant to show you how to cheat on the licensing cost. In fact, I will show you how to get tangible value by using Power BI free version so that you can easily justify the cost of the license with a strong and compelling business justification.
The idea is simple. Use Power BI just like we use Excel. Create Power BI Reports, save PBIX files on OneDrive / Teams. Share the reports with each other as links or attachments – exactly the way we do with Excel files. All users must have Power BI Desktop free version. This process does not cost any money. Power BI free version offers all the features and connectors with no time limit or restrictions.
Then the next question is – if this works for free, why pay Microsoft that 9$ per user per month license for Power BI Pro? Good question. Sharing PBIX files may seem satisfy your needs – but it has many disadvantages. Read on to find out more…
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:
Grouping means combining multiple items into fewer items. It helps us consolidate and summarize things to understand them at a higher level of granularity. Let us see how to use Power BI Grouping done easily and quickly. This is useful for Ageing Analysis, Bin or Bucket Analysis, week / custom date range analysis.
Continue reading Power BI Grouping
Power BI is becoming popular. Therefore, many companies are interested in considering a Power BI Pilot project as a proof of concept. While interacting with customers, I have noticed that many such pilots fail. The failure is NOT due to the capabilities of the product, but due to other factors which are controllable. In this article, I have listed a process which prevents common errors and improves the credibility of the outcome.