Data is exported from rows to columns

Looker Q&A Looker
, ,
1

Good morning, I would appreciate some support from this wonderful team.

Does anyone have a suggestion on how I can export the data in CSV or XLS format, just like the visualization?

In the visualization layer, the metric names are in rows, but in the export they are in columns.

This is necessary because the data will be used in another Excel spreadsheet that already receives this data in the model as it is in the visualization.

Would it be possible to do it this way?

Thank you very much.

2

Hello Danilo!

When you export data, most platforms default to a “Long” format (tidy data) even if your visualization is in a “Wide” format (Pivot table).

Here are the best ways to get your export to match your visualization:

  1. The “Pivot” during Export (Tool Specific)

Depending on which platform you are using, there is usually a toggle to maintain the layout:

  • Looker: When clicking “Download,” ensure you select “With visualization options applied” or “As displayed in the data table.” If you are using a Pivot in the Explore view, this should keep the metrics in rows.
  • Power BI: Instead of “Export Data,” use “Analyze in Excel.” This creates a live connection where the Pivot Table structure is preserved exactly as you built it.
  • Tableau: Use Worksheet > Export > Crosstab to Excel. This is specifically designed to mirror the UI layout rather than the underlying data grain.
  1. The strong textPower Query “Unpivot” (The “Set it and Forget it” Method)

If your BI tool refuses to cooperate, the most robust way to handle this is to let the export stay in columns and have Excel fix it automatically when you open the file.

  1. Open your “Destination” Excel sheet.
  2. Go to the Data tab > Get Data > From File > From Text/CSV (select your export).
  3. Click Transform Data.
  4. Select the columns containing your metrics.
  5. Right-click and select Unpivot Columns.

This will turn your columns back into rows. The beauty of this is that next month, you just replace the CSV file, click “Refresh,” and your spreadsheet updates perfectly.

  1. Using a “Transposed” View

If you have control over the SQL or the Data Model:

  • Try creating a specific “Export View” where you use a UNION ALL to stack the metrics manually.
  • Example: ```sql SELECT ‘Metric A’ as MetricName, ValueA as MetricValue FROM Table UNION ALL SELECT ‘Metric B’, ValueB FROM Table

Which BI tool are you currently using to view this data? If you let me know the specific software, I can give you the exact click-path to fix the export settings!

3

Good morning,

Thank you very much for your reply. I am using the Looker Platform for viewing and exporting the data.