This is a commonly asked question. I will try to answer it in the simplest possible manner. Of course, this is as of May 2018. Things change very fast. So please check online for the latest status. Power BI Free does exist. In two forms. One is built into Excel and one is a subscription option. Continue reading Is Power BI Free ?
This content is relevant only if you are a CIO (or IT decision maker). Here is the video of the session I conducted at CIO Power List event on 4th May, 2018, at Conrad, Pune. Shadow Analytics has been around ever since “shadows” – also called end users – are around. Everyone knows about. Some people tried to eliminate it. Nobody succeeded.
This 30 minute video explains how to use Shadow Analytics as an opportunity to empower rather than restrict users and improve effective utilization of data.
The demos included in this Shadow Analytics video are:
Flash Fill, Insights, Explain the increase and Q&A.
What is Shadow Analytics?
It is all kinds of data capture, clean-up, manipulation and report generation performed by end users without IT intervention.
If you generate a report from a business system (which is built or managed by IT), it is alright. But if you copy paste data from multiple such reports into Excel and then generate a new report, it becomes “Shadow Analytics”.
As you can imagine, it is difficult to eliminate it. Irrespective of how much time and effort you have spent on creating the most flexible ad-hoc reporting systems, it is impossible to provide every possible variation that users want. Therefore, Shadow Analytics has always been there and is likely to survive in the foreseeable future.
Problems associated with Shadow Analytics
Primarily two problems. It is extremely error prone and time consuming. There are lots of related problems. The root cause is that data is handled in a casual manner without regard for its recency and in a completely undocumented manner.
This can lead to wrong decisions, delayed decisions, increased operational risk and enormous wastage of precious time.
It is impossible to handle and correct the data sources and deliver data to users in a manner which is so easy that they stop doing the manual capture and clean-up altogether.
Once clean, accurate and updated data is available as input, creating reports can be done by end users in a more informed and productive manner.
Yes. This long-awaited feature is finally here with Office 365 Pro Plus (May 17). This is a short article showing the steps. Detailed video coming soon. Ability to Change Pivot defaults is very useful. You no longer need to customize each Pivot manually.
Continue reading Change Pivot defaults
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.
This is a short post. It is like an FYI mail. Excel never understood any dates before 1900. We got used to that limitation over the decades. But Power BI does understand Dates before 1900. The best part is, you do not have to take any specific action. It just works.
Here is the raw data and the Power BI output.
If you try this in Excel, it just will not work. Now that you know this, starting using Power BI with Dates before 1900.
Mind you, the Power BI documentation says that the earliest limit is 1900. It still works for dates before 1900. Drill down is also supported. Here is the same data at Day level.
This ability may make historians and archeologists partially happy. There time scales are huge and Power BI does not support that much of a range. But still, it is an improvement worth knowing about.
Cross-tab data is where you have row headings as well as column headings. Consolidating (combining) multiple blocks of such data is a laborious process. Usually the rows and columns do not match across data blocks. You need to do a lot of copy-paste to bring them in the correct order before you can add a formula. Using a 20 year old unknown but powerful feature of Excel you can Consolidate Cross-Tab Data in minutes. You just select the data blocks and let Excel do the dirty work of matching row and column names.
Additional Resources : Consolidate Cross-Tab Data
I am happy to announce a new, comprehensive course on Pivot Tables on Udemy. As a special benefit to blog readers, I am including a promo code for FREE access (available to first five readers, on a first-come-first-server basis). Read on for more details.
Here is a fast paced, 8 minute video which will revolutionize Pivot Report Sharing. Using OneDrive or SharePoint, control the visibility and provide full interactivity. Reports render instantly even with large files and low bandwidth.
Pivot Report Sharing articles
This is a sneak preview of my upcoming Udemy course on Pivot Tables. This video explains the relationship between Pivot Tables and File Size. Watch this 6 minutes video and learn from it.
Apply this knowledge to your existing pivots – especially those with large file sizes and see the difference. Make sure you try it out on a copy. Never learn using Live files.
Here is the list of articles I have written about Show Values As in Pivot table. This is a very important set of features but are mostly ignored.