How to Transform Google Sheets Data to Display Metrics in Rows and Weeks in Columns in Looker Studio

Hello Looker Studio Community,

I’m working on visualizing some data from Google Sheets, and I need help with formatting it in Google Looker Studio. Here's the situation:

Data in Google Sheets:

I have data structured as follows in Google Sheets:

Week Sales Impressions Clicks

2023-W01100050,0002,500
2023-W02120045,0002,800
2023-W0390048,0002,400

Desired Format in Google Looker Studio:

I want to display this data in Google Looker Studio in the following format, where the metrics (Sales, Impressions, Clicks) are shown in rows, and the weeks (2023-W01, 2023-W02, 2023-W03) are displayed in columns:

Metric 2023-W01 2023-W02 2023-W03

Sales10001200900
Impressions50,00045,00048,000
Clicks2,5002,8002,400

What I’ve Tried:

I’ve connected my Google Sheets data to Looker Studio and set up a Pivot Table, but I am struggling with how to format the data such that:

  • The metrics (Sales, Impressions, Clicks) are displayed as rows.
  • The weeks (2023-W01, 2023-W02, 2023-W03) are displayed as columns.

I’ve tried adding the week in the Columns section and the metrics in the Rows section, but the output isn’t formatted as I want it and Row Dimension does not take more than two dimentions.

Questions:

  1. How can I set up my pivot table to correctly display the metrics (Sales, Impressions, Clicks) in rows and the weeks in columns?
  2. Is there a way to dynamically create the structure where the metrics and weeks are aligned this way using calculated fields or any specific configuration?

Any help or tips on how to format my data correctly in Looker Studio would be greatly appreciated!

Thank you in advance!

0 1 922
1 REPLY 1

@jamshed 
1. Pivot table is correctly representing your numbers. Your data is one dimensional  hence the given layout.
2. Yes and no. Your  example  is table  and not  a  pivot table meaning you need to transpose your data first and create a table  in looker studio. However, you will need to  add  each week as separate column  which is probably not best idea.. 
3. You can introduce new dimension to your data but  this wont  also produce  result you require as its going to correctly pivot your data and show all in one row for your dimension.

I suggest try changing your habit from scrolling  horizontally to pivotally.
I understand it may be challenging at start but BI are bit different from Spreadsheets.

Regards
Arkady Zagdan