1619 Views
7 minute read
Categories
Azure Power BI

Dynamic Data Columns in Power BI – an Example of Creating a Table Transpose

We have often come across scenario where we need to shift the data based on some filtering criteria. So, we just need to eliminate the rows which are not part of the criteria which is the essence of filtering or slicing the data. Now if we come across a scenario when while creating a report where the number of columns keeps changing based on some filter value or in simple terms the number of columns is dynamic, it will be tedious to accomplish. Though this scenario is not common but having a solution to manage this unique use case would be nice to have.

Hence, let me run through a perspective before we delve into the technical aspect of this use case.

Let us think about a fictitious company ticket booking company airticket.com. Now the company has details of places a person has visited for last 10 years. This would be a valuable information that could have an upsell value for other agencies. Now imagine tens of thousands of customer data like this, and it is gold in terms of information to a hospitality company. However, representation of the data is simple person versus the places visited. The company would rather be interested to see the data other way around as in the places visited versus the persons. This makes the data more relevant for a hospitality company. Hence, we need to re-format and represent the data in a transpose manner.

Once we have the desire to see the data in the transpose format we have unknowingly accepted the number of columns in our table and essentially our chart to be dynamic.

Let us look at this example.

Let us say airticket.com has the below data for top 10 customers and the 10 places they have visited most in last 10 years.

Customers Places Visited
AidaHong KongHelsinkiFrankfurtMilanLas VegasMoscowTaipeiRomeCopenhagenBrisbane
AllieTaipeiRomeCopenhagenBrisbaneOrlandoOaklandHong KongMiamiAmsterdamMilan
EmilyHonoluluEdinburghParisMelbourneSydneyMiamiAmsterdamMilanLas VegasMoscow
EmmaParisMelbourneSydneyMiamiAmsterdamMilanLas VegasMoscowTaipeiRome
FaberRomeCopenhagenBrisbaneOrlandoOaklandHong KongHelsinkiFrankfurtMelbourneSydney
LoniBrisbaneSydneyMiamiAmsterdamMilanLas VegasMoscowTaipeiRomeCopenhagen
MaureenCopenhagenBrisbaneOrlandoOaklandHong KongHelsinkiFrankfurtSydneyMiamiAmsterdam
PeggyMelbourneSydneyMiamiAmsterdamMilanEdinburghHong KongRomeCopenhagenHonolulu
PeggyMelbourneSydneyMiamiAmsterdamMilanEdinburghHong KongRomeCopenhagenHonolulu
RosalineCopenhagenParisMelbourneSydneyMiamiAmsterdamMilanLas VegasMoscowTaipei
TeriLas VegasMoscowTaipeiRomeCopenhagenBrisbaneParisMelbourneSydneyMiami

As mentioned, before we will have to represent the data in a transpose manner to make it relevant for a hospitality company. So now the data will be represented as is shown below.

Places Visited Customers
AmsterdamEmilyEmmaRosalineLoniMaureenAlliePeggy  
BrisbaneLoniMaureenFaberAidaTeriAllie   
CopenhagenRosalineMaureenFaberAidaTeriLoniAlliePeggy 
CopenhagenRosalineMaureenFaberAidaTeriLoniAlliePeggy 
EdinburghEmilyPeggy       
FrankfurtAidaMaureenFaber      
HelsinkiAidaMaureenFaber      
Hong KongAidaMaureenFaberAlliePeggy    
HonoluluEmilyPeggy       
Las VegasEmilyEmmaRosalineAidaTeriLoni   
MelbourneEmilyEmmaRosalineFaberPeggyTeri   
MiamiEmilyEmmaRosalineLoniMaureenTeriPeggy  
MilanEmilyEmmaRosalineAidaLoniAlliePeggy  
MoscowEmilyEmmaRosalineAidaTeriLoni   
OaklandAllieMaureenFaber      
OrlandoAllieMaureenFaber      
ParisEmilyEmmaRosalineTeri     
RomeFaberEmmaPeggyAidaTeriLoniAllie  
SydneyEmilyEmmaRosalineFaberMaureenTeriLoniAlliePeggy
TaipeiLoniEmmaRosalineAidaTeriAllie   

Now to show this table or report would have the number columns varying depending on the selection of the places visited. Let us say you want to filter the data by the city Honolulu you will have just two columns, but if you choose Sydney you will have 10 columns. So that should set the sentiment of how we got this elegant data set which should have dynamic columns set in the report.

So far so good but how do we achieve this. For that aspect let us move to the technical part of this discussion and let us start from the humble data presented in the TicketOrderHistory table first.

Sample of the TicketOrderHistory

CustomerNameSourceDestinationNumberofTimeVisited
AllieNewarkAmsterdam3
EmilySan AntonioAmsterdam3
EmmaLyonAmsterdam3
LoniMumbaiAmsterdam4
MaureenMexico CityAmsterdam1
PeggyOsakaAmsterdam4
RosalineOaklandAmsterdam1
AidaAthensBrisbane1
AllieNewarkBrisbane2
FaberBudapestBrisbane4
LoniMumbaiBrisbane5
MaureenMexico CityBrisbane2

Now in this excerpt of the TicketOrderHistory table you can visualize how the data is designed to show the holiday destinations a person has visited along with the frequency of visit.

Now let us see how we can import this data into Power BI and how we can present this data in a simple chart to add meaning to the data.

Open a new Power BI Report and add a data source excel and import the data from the sheet TicketOrderHistory. Click on Transform and add a new Index Column to the table as is shown below.

Dynamic Data Columns in PowerBI
Dynamic Data Columns in PowerBI

Now let us create a very basic report in the sheet1 which we have renamed to MostVisitedDestination. Here we have presented the data for the customers vs the number of times they have gone for the holiday destinations as a sum. We have shown the data in form of a table and a bar chart. We have added two slicers one for the NumberofTimesVisited and the other for the Destination itself.

Dynamic Data Columns in PowerBI

Now we move to the next phase where we need to see the data in the transpose manner. To do that we will again edit the query and go to the query editor.

We will use the TicketOrderHistory table and create a reference to a new table. Rename the new table as CustomerName and remove all columns except CustomerName and Index.

Dynamic Data Columns in PowerBI

Go to transform in the Top Ribbon.

Click on the CustomerName column and click on Unpivot Columns.

Your CustomerName table will have a two new columns Attribute and Value.

The Attribute will have just one value that is CustomerName and the Value column will have the actual names of the customers. Key thing to see here is the Index number still maintains the right order in which the CustomerName appeared. It will help us put the pieces in the puzzle.

Dynamic Data Columns in PowerBI

Now we will click close and apply in the query editor.

Now let us add the final table that we need for representing the data. We will use a Matrix to represent this data.

We will first choose the Destination Table and select the dimension Value this will show the name of the Destinations in the left-hand side. Now we will choose the CustomerName table and then select the dimension value. At last we will choose the TicketOrderHistory Table and choose the measure NumberOfTimesVisited.

Dynamic Data Columns in PowerBI

So now if I want to see the people who visited Paris we will just need to choose Paris from the slicer and then you will see the Customer name columns modify dynamically based on your choice of the city.

Dynamic Data Columns in PowerBI

Now if you see closely the number of Customer Columns have changed from 10 to 4. This is grazing the surface of the dynamic columns in Power BI and there is lot to it. The idea behind this blog was to show how the dynamic columns in Power BI can be created and utilized.

Tushar And Chiranjib

About Tushar And Chiranjib

Tushar Kanti -

Possessing excellent management skills and ability to work with minimal supervision. Consistently achieve targets, implement best practices and SIP’s. Proven expertise in scripting and automation both on prem and in cloud.

Chiranjib Saha Roy-

Working as MS SQL Server Specialist with a decade of successful experience in supporting production and doing development.

LinkedinTwitterFacebook

Leave a Reply

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




Enter Captcha Here :