Pivot Transformation On Super Store Sales Data in Azure Data Factory

  • Published
  • Posted in Azure
  • Updated
  • 2 mins read

Azure Data Factory is Azure’s cloud ETL service for scale-out serverless data integration and data transformation. It offers a code-free UI for intuitive authoring and single-pane-of-glass monitoring and management. 

Pivot operation allows to summarize large amount data for easier data analysis and reporting. We have considered the Sales Date for Particular chain of stores in different locations. Our aim is to find total Sales in a particular state in a country 

Here is how this blog is structured:

  1. Uploading Sales file in Azure storage Account 
  2. Ingesting file in data Factory  
  3. Performing transformation on the ingested data 

Uploading Sales file in Azure storage Account:

 Superstore Sales file is uploaded in container by clicking upload button.

Ingesting file in data Factory:

  1. Initially linked Service is created which helps to link Azure Data Factory and Azure Storage account. 

2. Since file is csv inline dataset is used as source option in dataflow. Next file path to the required file is provided in source option with First Row as header.

 

3. Later Schema is imported which automatically detect data types  

Graphical user interface, application Description automatically generated

Performing transformation on the ingested data 

Columns which are not required are removed using select transformation and only required column are selected 

Data is grouped by Country during doing pivot operation. 

Graphical user interface, application Description automatically generated

To generate total sales of States in a particular region and country, State is used as Pivot key.

Aggregation is done based on sum to find total sales of state.

Data preview shows total sales of super store in each state.

Later obtained result is sink into preferred database 

Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com. 

Aniket Ghodinde
Data Engineer
Addend Analytics

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.

Email Us Or Contact Us