Select Page

Wrangling Data | Pivot/Un-Pivot Columns Feature | Which BI tool Wins ??

by | May 29, 2020 | latest3, Thursday | 0 comments

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

Requirement

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

Country Name20102011201220132014
India10,00013,93216,82918,63220,952
Current Table Structure – Where YEARS are in columns instead of ROWS

We need to pivot them as below

Country NameYearPopulation
India201010,000
India201113,932
India201216,829
India201318,632
India201420,952
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

https://data.worldbank.org/indicator/SP.POP.TOTL

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

Tableau

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

MicroStrategy

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

Qlik

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!!

Submit a Comment

Your email address will not be published. Required fields are marked *

Secured By miniOrange