Looker / Tableau crosstab export
The pivoted "crosstab" you download from a Looker Explore or a Tableau worksheet, mistaken for a rectangular data table when it is a picture of a formatted, totalled, row-capped visualization.
The shape, how the file is really laid out
Dimensions occupy both rows and columns, so the header is two or more rows tall
(hierarchical column headers) and the left row-labels repeat only on the first
row of each group, the rest are blank and must be forward-filled. Subtotal and
Grand Total rows and columns are baked into the grid alongside detail. Values
are formatted-as-text exactly as displayed: thousands separators ("1,234"),
currency symbols ("$1.2K"), percents ("45.0%"), parentheses for negatives
("(320)"), and em-dash or "Null" for missing. Looker's "with visualization
options" download is pivoted, formatted, totalled and row-capped (commonly 500),
while "results in table" is the unpivoted raw form; Tableau's Crosstab export to
CSV/Excel mirrors the sheet, abbreviations and all, and Excel crosstabs use real
merged header cells.
The traps
Summing the measure column double-counts, because the Grand Total row is already the sum and interleaved subtotal rows are aggregates sitting among detail, and nothing but a label distinguishes them.
- Every trap in this export, worked through
- The reconciliation anchor, the total the file asserts about itself
- The refusal cases, when to stop and ask instead of guess
Reference: Tableau Help, "Export views" and "Crosstab" export · Looker Docs, "Download data from an Explore" (formatted vs. unformatted, totals, row limits) · Looker Docs, "Table calculations"