Excel Data Model is a database that is built-in to Excel. It has been around since 2010. Using it increases the capacity of Excel to handle millions of rows, it reduces file size significantly and eliminates VLOOKUP for code to description mapping. These new options in Excel 2016 and above simplify the usage of Excel Data Model and improve performance for large data operations. Go to File – Options – Data tab.
Options in section 1 and section 2 are already covered in my previous blog articles. Section 3 helps us incorporate Data Model more easily into day-to-day Excel data management activities.
Continue reading Excel Data Model : Simplifying usage
PowerView button missing in Excel 2016 – Insert tab? It it visible in 2013. Here is how you add it.
Continue reading PowerView button missing?
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.
Continue reading Split column into rows
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.
I am happy to announce that my first detailed training course is now up and running at Udemy. It is about Power BI. But it starts with what you already know – Pivot Tables. That is why the course is called Pivot Table to Power BI.
Continue reading Announcing Pivot Table to Power BI course
Here is an interesting way to learn two things in one go. While creating the Power BI course for UDEMY, I created lot of explanatory videos. One of the DAX functions which is difficult to understand is the RELATEDTABLE function. So here is a dual video which explains DAX RelatedTable animation. 8 minutes.
Power BI and Power Map support mapping your own data using Latitude and Longitudes. Lat-longs are available in various formats. Power BI supports only decimal format. Unfortunately lot of data still comes with the DMS format Lat-Longs. I created a simple tool: Power BI Lat Long Converter. Use it do perform bulk conversions quickly. Continue reading Power BI Lat Long Converter