Select Page

Today i would like to discuss about the Pivot/Un-Pivot Columns feature easily in BI Tools (Power BI, Tableau, MicroStrategy, Qlik).


Data is looking like below for example that we bring into data modeling interface in any BI tool (via Self Service approach)

Country Name20102011201220132014
Current Table Structure – Where YEARS are in columns instead of ROWS

We need to pivot them as below

Country NameYearPopulation
You can see the expected way of data format in tabular structure

Now, lets see how can we do this on different BI tools & see who wins the race in the end to achieve this – Ready ?? !! Let’s go

  • Power BI
  • Tableau
  • MicroStrategy
  • Qlik

I will be using World Population data

Power BI – Microsoft

Sample Data
Click “Transform” & then “Unpivot Columns”
Rename is essential – right ?
Double click the column header to rename them
Sample Rename of header shown here
“Close & Apply” the Power Query Editor changes
Viz/Grid created with pivoted data


Connect to the data via Tableau
“Cleaned with Data Interpreter” – Isn’t it easy to clean the data easily ?? Love it!!
Switch to “Manage Metadata” section – Press CTRL & Select all YEAR Columns to Pivot
Right Click “Pivot” to perform the required wrangling
Post the Pivot – this is how it looks
Same as Power BI, click the header to rename as expected format you would want
Vola : Rename done!!
Now you got the Viz/Grid created


Open Dossier Interface & Click “New Data”
Select “File From Disk”
Click “Choose Files”
Post the selection of file, click “Prepare Data” to perform data wrangling
If source has multiple sheet, select needed sheet for analysis
Right click the table & Select “Parse”
Isn’t it good that, auto initial 2 rows deleted and kept 3rd row as header – Cool!! Love it!!
Click “Cross Tab”
Drag the cursor to select range of attribute header and attribute values
Easy – Pivot is complete now. Perform Rename as next step
Right click column to “Rename”
Viz/Grid created with extracted data


Connect to the excel file
excluding initial 2 rows is easy by typing the number of rows to clip – Easy right!!
Go to “Data Manager” editor window to perform Pivot
Select all year columns to pivot – It is manual – painful if more columns exists — Sad Part in Qlik on Interface!! Might be easy way there but i couldn’t find anything from my knowledge
Example of “Data Manager” interface to edit & perform “Pivot” action
Once columns are selected for Pivot – Click “Apply Unpivoting”
Rename – easy as other tools
Rename is complete
Cool Part is, Qlik shows default viz based on selection of columns – Isn’t it cool ? I love it!!

Final Conclusion

Who wins in this race ? 🙂 Comment your views lets hear it out!!!

My answer is each of the tool is equally easy to set this up. All win the race equally 🙂

Thanks for reading! Enjoy the day!!

Secured By miniOrange