Tag Archives: Analytics

Split column into rows

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

Split column into rows - raw data

And you get a report like this. No need to use formulas or do any manual work.

Split column into rows - Final Charts

You must have the May 2017 update for Power BI Desktop installed.

Continue reading Split column into rows

Power BI Dates before 1900

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.

Dates before 1900 - raw dataDates before 1900 - in a chart

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.

Drill Down

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.

Consolidate Cross-Tab Data in minutes

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 have already covered this topic as a step-by-step article.  Now I have created a video (9 min). You can download the sample file used in the video and try it yourself.

Continue reading Consolidate Cross-Tab Data in minutes

Pivot Report Sharing

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

New, Improved way of delivering Excel reports: Part 1
New, Improved way of delivering Excel reports: Part 2
New, Improved way of publishing Excel reports: Part 3

Pivot Tables and File Size

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.

Continue reading Pivot Tables and File Size