Fix shifted, missing and misread columns before publishing your dataset

If a file that used to import cleanly now produces shifted columns, missing fields or changed values, compare the original file with the import before editing cells. The problem may be in the export, the import settings or a saved transformation—not the dataset itself.

Keep the last known-good file and work on a copy of the new one. Your target is a table with the expected headers, record count and values, ready to publish without silently changing identifiers or dates.

1. Find the first point where the data changes

For CSV or TSV, open the file in a plain-text editor as well as your spreadsheet application. Check the header, a normal record and the first record that imports incorrectly. For XLSX, inspect the intended source worksheet directly.

Compare the new export with the previous working version:

• Did commas become semicolons or tabs?
• Were columns added, removed, renamed or reordered?
• Is there now a title or explanatory row above the headers?
• Does a text field now contain commas, quotation marks or line breaks?
• Has the date format or decimal separator changed?

If you exported from Excel, also confirm you selected the right worksheet: saving as CSV or text saves only the current worksheet, not the whole workbook. Microsoft’s import/export guide documents that limitation.

Use the symptom to choose the next check:

What you see	Check first	Next action
Every record lands in one column	The actual delimiter	Set the importer to comma, semicolon or tab as appropriate
Only some records shift sideways	Quotes around text containing delimiters	Repair malformed quoting in the export, then reimport
Extra rows appear inside text values	Embedded line breaks and the importer’s quote handling	Confirm quoted line breaks stay within a field
The first observation becomes a heading	Header handling	Confirm whether the source actually has a header row
IDs lose zeros, or dates change meaning	Automatic type conversion and locale	Reimport the original values with explicit types
New columns disappear during refresh	A fixed column count or later selection step	Inspect the saved query before changing the file

Excel lets you select a delimiter and inspect its effect in the import preview. Prefer that local setting over changing Windows’ list separator, which affects other applications too. Microsoft explains both options.

2. Check structure with a small example

This illustrative CSV has four columns and two data records:

record_id,place_name,observation_date,amount_usd
00127,"North District, East",2026-10-01,120.50
00128,"Community ""A"" Centre",2026-10-02,180.00

The expected result is:

• record_id: text values 00127 and 00128;
• place_name: one field per record, including the comma or literal quotation marks;
• observation_date: October 1 and October 2, 2026;
• amount_usd: numeric values totaling 300.50.

For conventional comma-delimited CSV, fields containing commas, double quotes or line breaks should be enclosed in double quotes. A literal double quote inside such a field is doubled. Headers and records should have the same field count. These conventions are documented in RFC 4180.

Do not count commas or physical lines as a validation shortcut. A quoted comma belongs to a value, and a quoted line break can belong to one record. Use a CSV-aware importer to check field and record counts.

Quotes solve field boundaries; they are not a substitute for setting the identifier column’s type. For a separate header problem, see CSV header row vs data row.

3. Reimport with explicit settings

For Excel versions with Get & Transform, start with Data → From Text/CSV, rather than simply opening the CSV. Excel’s ordinary CSV opening uses default format settings to interpret columns, which can change dates or remove leading zeros. Microsoft’s guide describes the distinction.

In the Text/CSV preview:

1. Choose the delimiter that matches the file.
2. Set File Origin to the file’s actual character encoding if accented names look corrupted.
3. Turn off automatic data type detection for the diagnostic import, so columns start as text.
4. Select Transform Data to review the table before loading it.

If quoted text splits into extra rows, edit the Source step and check Line breaks: Ignore quoted line breaks keeps an embedded break inside its field. These options are documented for the Power Query Text/CSV connector; availability can vary by host application.

In Power Query, confirm the header row, then assign types deliberately: identifiers as Text, amounts as the appropriate numeric type, and dates using the source’s documented convention. Automatic detection can infer headers and types from the first 200 rows, so later values may not fit its initial guess. For day/month/year dates, use Change Type → Using Locale with the matching locale. Microsoft’s data-type documentation explains both behaviors.

When repairing an existing query, inspect its Changed Type step rather than just adding another conversion at the end. If an earlier conversion already removed zeros or misread dates, return to the original source values. Changing the display format afterward does not establish what the original value meant.

If refresh drops newly added columns

Inspect the query’s source and subsequent transformation steps. In Power BI Desktop, Microsoft documents that an initial CSV import can fix the column count; later refreshes then omit columns beyond that count. The connector’s troubleshooting section describes this case.

In an M query, check for an option such as Columns=4 inside Csv.Document. Microsoft specifies that a count smaller than the input ignores additional columns. Update it to the reviewed schema—or omit the count to let the importer determine it from the input—then inspect downstream steps. Do not remove the limit without checking what new fields contain. Csv.Document reference.

4. Verify the public table, not just the import preview

Before publishing, compare the repaired table with the source:

• expected header names and column count;
• data-record count, excluding the header;
• first and last records, plus records near the original failure;
• exact identifier strings and documented date meanings;
• missing-value counts and a control total for numeric fields.

As of October 27, 2026, TablePage’s homepage lists CSV, TSV, XLSX and XLS uploads and describes the result as a public dataset page. Upload only data cleared for public release—never sensitive information.

After publishing, repeat the value checks on the resulting page. For the example above, confirm both IDs retain their zeros, both place names remain in one column and the amounts are unchanged. If the reviewed file imports correctly locally but the published page differs, isolate the mismatch with a small, nonsensitive sample before changing the source again.

Record the export date, delimiter, encoding, header names and type rules alongside your working files so the next refresh has a known baseline.