Clean data for dashboards without a script, a macro or a separate preparation tool.
Spreadsheet exports arrive with merged headers, blank cells, one column per month and "N/A" where a number should be. Klayara fixes those on the way in, shows every step on real rows before you commit, and keeps the result current on a schedule.
No-code preparation stepsPreview on real rowsScheduled and incremental refreshNever a half-loaded table
What is a data preparation tool?
A data preparation tool cleans and reshapes raw data, such as spreadsheet exports or database tables, so it can be charted and trusted: removing unwanted rows, fixing text, filling blanks and turning wide sheets into tidy lists. In Klayara these steps are applied without code as data comes in, each previewed on real rows, and refreshed on a schedule or incrementally.
How do I clean Excel data for a dashboard?
Clean Excel data by fixing it once, as it comes in, rather than by hand every month. A typical spreadsheet workflow repeats the same tidy-up each time a new export arrives: delete the header rows, trim spaces, fill the blanks, replace "N/A", and rebuild the pivot. Each step is a chance for a mistake that nobody notices until a board meeting.
In Klayara you drag in the file, check how its columns map onto a new or existing table, and add preparation steps that run every time. Re-uploading next month replaces exactly that file's rows, so the same clean-up applies automatically and last month's figures stay as they were.
- Choose the columns you need; the rest never arrive
- Filter the rows with and/or conditions before they land
- Replace values across a column, such as "N/A" to blank
- Clean up text: trim stray spaces, fix upper, lower or title case
- Fill down blank cells the way spreadsheet exports leave them
How to unpivot a spreadsheet with one column per month
Unpivoting turns a wide sheet, with one column for each month, into a tidy list with one row per item per month. Budgets, forecasts and sales targets are usually kept in that wide shape because it is easy to type into, but charts and formulas need the long shape to filter by month or compare against actuals.
Klayara's unpivot step does the conversion as the sheet is loaded. A budget with branches down the side and April to March across the top becomes three columns: branch, month and amount. That table can then sit next to actuals from your accounting system on a budget-vs-actual dashboard, with no copy-and-paste in between.
No-code data transformation, previewed on real rows
Every preparation step in Klayara shows its effect on real data before you commit, with a quality bar that shows blanks and problems at a glance. You see the result of filling down or splitting a column on your own rows, not on a sample someone typed up for a demo.
Beyond the clean-up steps, you can add a calculated column from a formula, split one text column into several on a separator or merge several into one, and summarise detailed rows into totals as they are loaded. Small reference tables, such as targets, budgets or a mapping of old product codes to new ones, can be typed or pasted straight into Klayara and edited in place.
- No-code summary tables: drag columns and measures to create a summarised table
- Analysts can write their own query to combine tables and preview the result before saving
- Change what an import pulls in later and see exactly which columns will change
Scheduled data refresh and incremental refresh
Scheduled refresh keeps each table current without anyone remembering to run it: hourly, nightly or on demand. Every dashboard shows when its data was last updated, so readers know whether they are looking at this morning's numbers or last week's.
Incremental refresh fetches only new or changed rows, so refreshes stay quick on large tables such as sales lines or ledger entries. For each table you choose how a refresh lands: replace everything, append new rows, or update in place. A full mirror also removes rows that were deleted at the source. Another system can start a refresh too, for example right after the nightly close in your ERP.
Refreshes you can trust, and changes you can check
A refresh in Klayara swaps in whole or not at all, so readers never see a half-loaded table with this month's first branch and last month's others. If a refresh fails you get one alert, and another when it recovers; after three failures in a row the schedule pauses itself instead of failing silently every hour.
Housekeeping does not require starting over. You can change a column's type, bring back a removed column, remove one bad file's rows, duplicate a table or pick up new source columns without re-importing. Before renaming or deleting a column, an impact check lists every chart, formula, rule and filter that uses it.
- Sync status and run history sit right next to the data
- Table settings keep a version history you can restore from
Where data preparation fits in Klayara
Preparation is the step between connecting a source and modelling it. Klayara connects to 50+ data sources, from Excel, CSV and Google Sheets to databases and ERPs such as Tally, SAP Business One and Odoo, and each table can be queried live or imported. The preparation steps on this page apply to imported tables, which is where messy spreadsheets usually arrive.
Once a table is clean, you relate it to other tables in the semantic layer, define shared metrics in formulas, and build charts on top. Because the preparation runs on every refresh, the dashboards stay correct as new data arrives, without an analyst repeating the same clean-up.
From first click to everyday use.
Bring the data in
Upload an Excel or CSV file, connect Google Sheets, or pick a table from a database or ERP, and map its columns.
Add preparation steps
Filter rows, fix text, fill down, replace values, split, unpivot or summarise, without writing code.
Preview and commit
Check every step on real rows with the quality bar, then save the table when it looks right.
Set the refresh
Choose hourly, nightly, on demand or on a signal, and full or incremental, with alerts if a refresh fails.
Common messy-spreadsheet problems and the step that fixes them
| Problem in the file | Step in Klayara | Result |
|---|---|---|
| One column per month (Apr, May, Jun...) | Unpivot wide sheets | A tidy list of item, month and amount that charts can filter |
| Branch name only on the first row of each group | Fill down | Every row carries its branch, so totals by branch are correct |
| "N/A", "-" or old codes mixed into a column | Replace values | Blanks or current codes, so sums and counts work |
| " Dubai", "DUBAI" and "dubai" counted separately | Clean up text | One value per city after trimming and fixing case |
| Full name and code in one cell, "1001 - Acme Ltd" | Split columns | Separate code and name columns |
| Subtotal and blank rows in the export | Filter the rows | Only real data rows reach the table |
| Thousands of invoice lines when you need monthly totals | Group into totals | A smaller summarised table that loads quickly |
| Margin missing from the export | Add a calculated column | Margin worked out from sales and cost as data loads |
Everything included: prepare and refresh.
Shape the data on the way in, preview every step on real rows, and keep it current without anyone babysitting a refresh. See every feature.
Choose the columns
Keep only the columns you need; the rest never arrive.
Filter the rows
Drop rows you don't want before they land, with and/or conditions.
Replace values
Swap one value for another across a whole column — "N/A" to blank, old codes to new ones.
Clean up text
Trim stray spaces and fix upper, lower or title case in one step.
Fill down
Carry the last value down into blank cells, the way spreadsheet exports leave them.
Add a calculated column
Create a new column from a formula while the data comes in.
Split and merge columns
Split one text column into several on a separator, or join several into one.
Group into totals
Summarise detailed rows into totals as they are loaded.
Unpivot wide sheets
Turn a spreadsheet with one column per month into a tidy list that charts can use.
Preview on real rows
Every preparation step shows its effect on real data, with a quality bar, before you commit.
Type-in reference tables
Type or paste targets, budgets and mappings straight into a small table and edit it in place.
No-code summary tables
Drag columns and measures to create a new summarised table without writing code.
Combine tables with a query
Analysts can write their own query to combine tables and preview the result before saving it.
Change an import later
Edit what an imported table pulls in and see exactly which columns will change before you save.
Table housekeeping
Change a column's type, bring back a removed column, remove one bad file's rows, duplicate a table, or pick up new source columns — without re-importing.
Impact check before you change
Before renaming or deleting a column, see every chart, formula, rule and filter that uses it.
Scheduled refresh
Hourly, nightly or on demand; every dashboard shows when its data was last updated.
Incremental refresh
Only new or changed rows are fetched, so refreshes stay quick on large tables.
Replace, append or update in place
Pick how each refresh lands; a full mirror also removes rows deleted at the source.
Never half-written
Readers never see a half-loaded table: a refresh swaps in whole or not at all.
Refresh on a signal
Another system can start a refresh — for example right after the nightly close.
Refresh health alerts
One alert when a refresh fails, one when it recovers; after three failures in a row the schedule pauses itself.
Sync status and run history
See when each table last refreshed and any errors, right next to the data.
Data preparation questions, answered.
Something else on your mind? Ask us directly — a person answers.
Do I need to write code to prepare data in Klayara?
No. Choosing columns, filtering rows, replacing values, cleaning text, filling down, splitting and merging columns, grouping into totals and unpivoting are all point-and-click steps. Analysts who prefer it can also write their own query to combine tables and preview the result before saving.
What happens when I upload next month's spreadsheet?
Re-uploading a file replaces exactly that file's rows and runs the same preparation steps again. Rows from other files in the same table are left alone, so you can keep adding monthly exports without duplicating or losing earlier months.
How often can data refresh?
Tables can refresh hourly, nightly or on demand, and another system can start a refresh with a signal, for example right after the nightly close. Incremental refresh fetches only new or changed rows, which keeps refreshes quick on large tables.
What happens if a refresh fails?
Readers keep seeing the last complete version, because a refresh swaps in whole or not at all. You get one alert when it fails and one when it recovers, and after three failures in a row the schedule pauses itself. Run history shows the error next to the table.
Can I change a column without breaking dashboards?
Yes, and you can see the risk first. Before renaming or deleting a column, the impact check lists every chart, formula, rule and filter that uses it. Column types can be changed and removed columns brought back without re-importing the table.
Is data preparation included on every plan?
Yes. The preparation steps, scheduled and incremental refresh and refresh health alerts are available on every plan, including Starter. Growth adds features such as row-level security, automations and Klayara AI; see the pricing page for the full comparison.
Do preparation steps work on live database tables?
The preparation steps apply to imported tables, where messy files usually arrive. Live tables are read in place so nothing is copied. To shape a live table, use formulas, lookup columns and friendly names, or import the table when it needs the full set of cleaning steps.
See it on your own data.
Bring one report your team relies on. We'll rebuild it live in Klayara, with your permissions, so you can judge it on real numbers.
Prefer to see plans first? See pricing