Clean up messy data

Turn a messy table into one you can trust, fix it once so the fix sticks, and write the rule down so later answers follow it.

Clean and organize your data 1:49 Tell the Librarian your rules, and it builds clean views and groups them by topic.

Leer la transcripción

What the Library is for

The Querri Library is where you clean, organize, join, and prepare your data, to get fast, consistent answers for you and your colleagues. Most data starts out a little messy… The same customer shows up under different names, refunds sneak into sales, and every report makes its own call about what counts.

How the Library is laid out

In the Library, you sort that out once. Your files and connected apps sit around the outside. Toward the center are the questions, metrics, and rules that give them meaning.

Organize it by talking to the Librarian

And you don’t file any of it by hand. You just talk to the Librarian. Tell it what you’re trying to answer, and it does the organizing. Say, “sales means paid orders only.” It saves that as a rule, and Querri takes it into account when it answers questions on that data.

Build clean views

Ask it to build a view, and it joins, cleans, and pre-calculates the raw data into one tidy table… and it rebuilds after each sync.

Group everything into collections

Views, questions, and metrics are grouped into collections, one for each part of the business, like Revenue and Sales. Zoom in, and you can see what feeds what. Metrics sit at the center, each defined once, so everyone calculates them the same way.

See where an answer came from

So when someone asks a question, the answer reads from that same Library. You can see which view it used, check exactly how it was calculated… and watch the path light up. Clean it up once, keep it organized by talking to the Librarian, and every answer starts from the same definitions.

Before you start

• Permissions: anyone who can open a table can look at its columns and attach a fact. Cleaning in a project adds steps, so you need to be able to edit the project. Only admins can open Nightly insights.
• Data you need: a table that looks wrong. The example uses Curio’s marketplace_orders_raw, a marketplace export loaded exactly as it came. See Explore the sample company.
• Credits this uses: looking at columns and attaching a fact cost nothing. Each question in a project or to the Librarian uses a credit, and a view the Librarian builds uses one more.

Steps

1. Find the mess

Open the table in the Library and click a column’s name to open its panel. The chart and numbers there show most problems.

1. Top values: the most common values. The same channel spelled three ways shows up three times.
2. Null: the share of empty rows.
3. Distinct: how many different values there are. An ID column should have one per row.

A date or amount column with Top values instead of a chart is stored as text.

Curio’s marketplace export has most of the usual problems: channel names spelled many ways, dates in seven formats, revenue stored as currency text, and order IDs that appear twice with different amounts.

Problem	What it looks like	What it does
Names spelled differently	”Acme Paper” and “ACME Paper Co.”	Splits one customer into several rows
Mixed date formats	03/04/25 next to 2025-04-03	Puts orders in the wrong month
Numbers stored as text	”$1,204.50”	The column can’t be added up
Mixed units	cents next to dollars	A total 100 times too big
Blanks	empty cells, “N/A”, ”-“	Rows drop out of filters and averages
Duplicates	the same order twice, or in two systems	Counts it twice
Extra header rows	a title and a date above a spreadsheet’s real headers	The headers end up as data

2. Fix it at the source if you can

If a mistake starts in one system, like a name typed two ways in your CRM, fix it there, and everything that reads from it gets the fix after the next sync. If you can’t, or the data is an export like Curio’s, fix it in Querri.

3. Clean it in a project

Ask in project chat, and spell out each fix. For example:

Clean marketplace_orders_raw: trim and lowercase the channel names, parse order_date_raw into a date and gross_revenue_raw into a number, and keep one row per marketplace_order_id.

Querri adds a step built on the raw table, usually Data Cleaning or Data Query. For Curio, the channel names should come down to six marketplaces: amazon, faire, walmart, etsy, wayfair and target plus.

Say what Querri can’t guess. Which copy of a duplicate wins when the amounts disagree? Does 03/04/25 mean March or April? Is a blank a zero, unknown, or a row to drop?

A project step suits one analysis. The clean version lives in that project, and questions to the Librarian won’t read it.

4. Or clean it once in a view

When the whole team asks about this data, clean it in a view instead. Ask the Librarian with the same detail:

Build a view of marketplace orders with one row per order, the channel names standardized, dates as dates and revenue as a number.

A view rebuilds after each sync, so the clean version stays current. Querri reads it for the questions it clearly answers, not every question about this data. See Views.

5. Write the rule down as a fact

A view or a step fixes the data, but it doesn’t tell anyone why. A fact does, and when Querri writes a query against a table, it takes that table’s facts into account.

1. Open the column’s panel, for example gross_revenue_raw.
2. Click Attach a fact.
3. Write the rule where it asks “What do you know about this column?”, for example “gross_revenue_raw is currency text. Convert it to a number before adding it up.”
4. Click Save.

The fact is saved as a note. Open it and change its kind to data quality (see Facts).

Or tell the Librarian the rule in chat. It records settled rules as facts on its own, so read what it wrote.

6. Let the nightly pass keep watch

In Nightly insights, an admin can check that Profile data and joins is on. Each night it measures column health and brings what it finds to your Inbox. Nothing changes until someone accepts a card.

Check your work

• An ID column’s Distinct matches the table’s row count.
• Top values lists each name once.
• A total matches a number you trust, like the marketplace’s own statement.
• The rule shows under Facts attached.

Troubleshooting

Too many rows disappeared. Ask how many rows each fix removed, and say what to do with blanks instead of dropping them.

You still see duplicates. The copies may differ by a hidden space or a capital letter. Ask to trim and lowercase the ID before removing duplicates.

A total is 100 times too big. One column is in cents and another in dollars. Say which unit each uses, and record it as a fact.

Dates landed in the wrong month. 03/04/25 can be March 4 or April 3. Say which comes first, the day or the month.

A spreadsheet’s headers ended up as data. A title or blank rows sit above the real headers. Say which row holds them, for example “use row 6 as the headers”.

Next steps

• Bring scattered data together: join the clean table to your other systems.
• Facts & Rules: how facts shape later answers.
• Table and column panels: everything a column’s panel shows.