Tableau troubleshooting 01: When source figures are missing or do not match
An item in the source data does not appear in Tableau. A value that should be there is missing. Or a displayed value differs from the source.
Changing filters on the screen may not reveal the cause. Figures pass through several stages before reaching the screen: selecting input ranges, preparing data, loading it, aggregating it and displaying it. Finding where a value or its treatment changed helps distinguish missing data, calculation errors and different expectations about aggregation.
This article explains what to check by following the path from source data to its display in Tableau. We first establish the data flow and identify the data Tableau actually uses. We then explain the principle of correcting the process and regenerating its results, before examining five common causes and how to investigate them. Finally, we cover data comparisons and screen checks before publishing or updating.
1. From source data to its display in Tableau
Source data may be used in Tableau as it is, or the person building the workbook may first prepare it in Excel, Tableau Prep or another tool. Specifying a file or database so that Tableau can read its data is called making a connection.
Preparation is not always necessary. When it is, it may involve standardizing names, converting amounts to common units, organizing columns, combining annual tables or matching records to a classification table. For example, a table mixing thousands of yen and millions of yen needs consistent units before its amounts can be added together.
In a Tableau data source, you specify which files and tables to read, how to combine tables, and whether each column should be treated as numbers, text, dates or another type of data. One Excel workbook does not necessarily become one table or chart on screen. Several sheets may be combined, or selected rows used, to prepare the data for a view.
The view also needs settings for how to group and add figures, which years or categories to include, and how many digits to display. The figures on screen are the result of these operations and settings.
2. Identify the data Tableau is actually reading
Figures displayed in Tableau are not necessarily read from the original Excel file each time. Tableau supports extracts, which store a copy of the data, and live connections, which query the connected source.
Creating an extract converts all or part of the source data into a format Tableau uses for aggregation and display, and saves it in a separate file. Tableau calls this saved data an “extract.” In this article, we refer to it as “extracted data” to distinguish it from the operation of extracting it.
The current file format for extracted data is Hyper, with the .hyper extension. Views connected to extracted data calculate and draw their charts from the values stored in that file.
Consequently, changing the original Excel file without refreshing the extracted data can leave the previous values on screen. Creating extracted data can involve filtering rows or aggregating values in advance, so check which parts of the source were saved. Tableau documentation: Extract Your Data
A live connection queries a connected database or other source to produce the view. Check the connection destination and the point in time represented by the displayed data. Tableau documentation: Refresh Data Sources
The .twbx format commonly used to share Tableau files packages the workbook, which defines the views, together with related data files, images and other resources. It may contain extracted data, while some connections may still refer to external databases. Inspect what data is actually being used rather than relying on the filename. Tableau documentation: File Types and Folders
The stages to inspect can be organized as follows.
| Stage | What to check |
|---|---|
| Source data | Version, sheet, year, scope and stored values being compared |
| Preparation | Whether required rows, columns and values remain, and how names, units and classifications were standardized |
| Connection and creation of extracted data | Files and tables actually referenced, loading settings, stored values in extracted data and refresh status |
| Aggregation and display | Grouping, rounding, filter conditions and processing order, and changes caused by screen interactions |
With a live connection, aggregation and display use the connected source without the step of creating extracted data.
3. Correct the process or settings and regenerate the results
One mismatched figure may indicate that other records using the same process or settings are also affected. With tens of thousands of rows, correcting only the figures that catch your eye tells you little about the rest. Relying on chance discoveries does not provide enough evidence to judge whether the tool can be trusted.
Accumulating manual fixes to individual results does not make the process that produced them more reliable. Editing parts of the finished data by hand can mean repeating those edits at the next update, or losing them altogether.
At Rokugeisha, our approach is to correct the process that caused the discrepancy and regenerate from the source data, rather than adjust displayed figures simply to make them agree. We incorporate the correction into the production steps so that the results can be reproduced using the same procedure.
First, preserve the state in which the problem occurred. Save the reference source data, intermediate files and preparation instructions, and the Tableau file. Investigate using copies, and record the rows and screen conditions where the difference appeared. Refreshing extracted data first can make it harder to establish what happened.
Then compare the source data, prepared data, connected tables or extracted data, and displayed values in sequence. Identify where the difference first appears, correct the responsible process or setting, and regenerate the results from that stage onward.
If preparation dropped rows, correct the input range, prepare the data again from the source and refresh the extracted data. If values changed when extracted data was created, correct the loading settings and recreate that data. If only the view’s aggregation or filters caused the problem, correct those settings and verify the displayed results. In that case, the source data does not need to change.
After regenerating, compare all data subject to the same processing programmatically, and check screen behavior. Section 5 explains how.
4. Possible causes and how to investigate them
The following are five representative causes. Identifying the stage where each can arise helps narrow down what to compare and what to correct.
Possible cause 1: Rows or columns were lost during preparation
Suppose an Excel table has a blank row in the middle, with more data below it. If preparation takes only the rows above the gap, the remaining data never reaches Tableau. If the selected column range ends too early, amounts for later years in columns to the right may also be omitted.
Start by comparing row and column counts before and after preparation. Count data rows separately from headings and notes, and compare the names of the required columns. If a process that should preserve rows and columns reduces their number, investigate the selected range and intermediate steps.
Equal counts do not rule out a missing row offset by a duplicate. Also compare values that identify records, such as product IDs, to establish which rows disappeared or were added. If rows were intentionally aggregated or columns reshaped, check against the intended transformation.
Once the cause is identified, correct the input range or preparation steps and regenerate the data. Pasting only the missing rows into the finished table leaves the same problem liable to recur.
Possible cause 2: NULL and zero were treated incorrectly
An item name, such as a project or product name, may be present while its amount is blank. Tableau uses NULL to represent a missing value. Zero means an amount has been recorded as zero; the two have different meanings.
Suppose one product has an amount of 100 and another has no amount entered. Replacing the missing amount with zero gives an average of 50 across the two products. Averaging only the product with a recorded value gives 100. The appropriate treatment depends on whether the missing entry means “no sales” or “not yet reported.”
For the source data, prepared data, and connected tables or extracted data, count missing amounts and zeros separately, and match the records by product ID. Establish whether the value was absent from the start, was lost along the way, or was replaced with zero. Check the actual loaded values to see how blank Excel cells were handled.
Once the meanings of missing values and zero have been established, correct any replacement steps or Tableau calculations. If values disappeared during loading, the data types discussed next are another possible cause.
Possible cause 3: The wrong data type was used
A column’s data type specifies whether it is treated as numbers, text, dates or another kind of data. Numeric types include integers and types that support decimal values. Check whether amounts containing decimals are being read as integers, or apparent numbers are stored as text.
Excel may display 100 while the stored value is 100.4. Check the displayed precision separately from the stored value. Similarly, treating a product ID such as “001” as a number loses its leading zeros. A column containing digits does not always belong in a numeric type.
Inspect the column types and match original and loaded values using product IDs or other suitable keys. Values with decimals, text mixed into a column, or IDs with leading zeros can help identify the kinds of values affected.
The outcome of a type mismatch depends on the file format and how the data is read. Values may become missing, change, or produce errors. Check both the settings and the actual values. After correcting the type or conversion, regenerate and compare the data or extracted data produced by that step. Tableau documentation: Data Types
Possible cause 4: Grouping or rounding timing differs
Even when all data is present, aggregation can change the figures on screen. Consider this fictional example.
| Product ID | Product name | Amount |
|---|---|---|
| A01 | Apples | 100 |
| A02 | Apples | 20 |
Grouping by product name gives 120. Displaying each product ID separately gives 100 and 20. The appropriate view depends on what you want to examine. To check the products separately, distinguish them by ID even when their names match.
Rounding requires similar attention. Rounding 0.4 and 0.4 individually to the nearest integer and adding them gives 0. Adding them first and rounding 0.8 gives 1. When comparing Excel and Tableau, align both the rounding precision and whether rounding occurs before or after addition.
If decimal places are merely hidden on screen, calculations may still use the underlying decimal values. Check both the values used in calculations and those displayed.
Establish the reference grouping and rounding rules, calculate the expected values from the source, and compare them with Tableau’s results. If aggregation or rounding already occurred during preparation, correct that step. If the cause lies in a Tableau calculation or display setting, correct that setting.
Possible cause 5: Filters are not applied in the intended order
“The ten products with the highest sales in a region” and “products sold in that region among the top ten across all regions” need not be the same list. The result depends on whether the data is restricted to the region before ranking, or the regional condition is applied to the overall ranking.
In Tableau, filter types determine the processing order. Calculations do not necessarily follow the order in which someone clicks conditions on screen. For example, combining a regular region filter with a top-ten product-name filter may not produce the top ten products within that region. One way to apply the regional restriction first is to make it a context filter. Tableau documentation: Order of Operations
First establish the intended question. For the top ten products within a region, restrict the source data to that region, total sales by product and rank them. Compare that list and its amounts with Tableau’s displayed results. Check the product identities as well as the number of products and total amount.
If the cause lies in Tableau’s filter settings, the source data does not need to change. Correct the settings and verify the display for multiple regions or conditions.
5. Checks before publishing or updating
Some of these problems first come to light when someone happens to notice a discrepancy on screen. With tens of thousands of rows, waiting for such discoveries is insufficient. Build data comparisons and screen checks into the production steps, both after a correction and before initial publication or a data update.
Establish the reference and scope
Identify the reference source data and the Tableau version to be published or updated. Specify the years, rows and columns in scope, unit conversions, the meaning of missing values, grouping and rounding rules. Record the conditions for any intentional exclusions.
Compare data programmatically
Match the source data, prepared data, and connected tables or extracted data by product ID, year and other relevant keys. Think of placing two tables side by side in Excel and comparing cells for the same product. Where preparation converts units, compare against values that reflect that conversion.
Use a program to compare all data in scope: row counts and required columns, missing or duplicate IDs, counts of NULLs and zeros, and agreement in amounts and classifications. For each discrepancy, record the matching ID, values at the compared stages and the nature of the difference. People do not need to read through every row in pairs.
Prepare the reference values from the source data and the agreed aggregation rules. Running the production process again and obtaining the same result does not reveal errors in that process. For example, calculate amounts by product ID separately from the source, then compare them with the produced data.
Check aggregation and screen behavior
After comparing the data, specify the views and interaction conditions to check. For operations such as selecting a year or category, or clicking one chart to filter another, compare the expected records and amounts calculated from the source with the actual displayed results. Include conditions that previously exposed a problem.
A worksheet listing numbers can make inspection easier. Screen-specific behavior, such as filtering by clicking a chart, also needs to be checked in that screen.
Record the scope and results
Report the number of records compared, matches, discrepancies and their details, along with the views and interaction conditions tested. For example, distinguish “amounts were compared for every product in the target year” from “the display was checked under three specified combinations of year and category.” A claim that everything was checked needs to say what “everything” includes.
Preserving the comparison rules and program allows the same checks to run at each update. Review the comparison conditions whenever the data structure or aggregation policy changes.
Comparison with the source checks whether figures reach the screen through the intended processing steps. If the source itself appears to contain errors, or the aggregation policy needs clarification, discuss that with the people responsible for the material or the business process. Record what has been verified, what needs correcting and what still requires a decision.
Part 2, “It opens, but you cannot change it,” covers what to agree and hand over when the people building and using the tool are different.