nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Drop empty columns from pivot table using Table Transformer in Confluence Cloud

Teresa J Felder

I am trying to drop empty columns from a pivot table to reduce the width of the table to make it easier to see on the page.

I have 67-69 date columns in a pivot table that I am trying to review and drop if they have no value. On review of other questions via AI and this community that were somewhat related I found a sample code snippet that I adjusted to my content. Everything is saving, but the columns are not being dropped from the Pivot Table. Tried many different changes, but cannot seem to get it to work. Any thoughts?

SET @hasValue = (SELECT SUM("Jun 15,2026") FROM T1) IS NULL;
IF @hasValue BEGIN
ALTER TABLE T1 DROP COLUMN 'Jun 15,2026';
END;
SELECT * FROM T1

Accepted answers

All comments

Mia Tamm _Simpleasyty_

Hi @Teresa J Felder,

This is a really interesting use case, especially when dealing with 60+ date columns in a pivot table!

We're actually working on this exact functionality in Simple Tables for Confluence, and the good news is that we've already implemented it. We're planning to release it to production later this week.

The new Hide empty columns option will automatically hide columns where all values are empty, keeping your pivot tables compact and easier to read without writing SQL or manually removing columns.

The underlying data remains untouched, and columns containing zero values will remain visible, since zero is still a valid value.

We're also preparing a short video to demonstrate how it works.

Thanks for sharing this use case! It's great to see real-world examples of the problems we're trying to solve.

Hope this helps!

Tomislav Tobijas

Hi @Teresa J Felder,

Since the Table Transformer and Pivot Table macros are part of the Table Filter, Charts & Spreadsheets for Confluence Marketplace app developed by Stiltsoft, I'd recommend reaching out directly to the vendor.

They should be able to help you troubleshoot your SQL query and confirm whether dynamically hiding empty columns is supported, especially when dealing with 60+ date columns.

You can contact them through their support portal or the Support section of the Atlassian Marketplace listing.

Hope this helps! 🙂

Cheers,
Tobi

Mikhail “in Peru”

The reason nothing happens is one character. In ALTER TABLE T1 DROP COLUMN 'Jun 15,2026' the single quotes turn the name into a text string, not a column name, and the parser rejects the statement. Table Transformer runs on AlaSQL, and in AlaSQL a column name with spaces or commas goes in square brackets (backticks work too):

SET @hasValue = (SELECT SUM([Jun 15,2026]) FROM T1) IS NULL;
IF @hasValue BEGIN
ALTER TABLE T1 DROP COLUMN [Jun 15,2026];
END;
SELECT * FROM T1

I ran your script against plain AlaSQL (4.19) on a small test table. With the quotes it stops with a parse error at the column name. With brackets it drops the empty column and keeps the one that has values. I could not run it inside the macro itself, so whether Table Transformer lets you ALTER its T1 is worth a quick confirmation from Stiltsoft, but the quoting is what blocks you first.

Three things worth checking once it runs.

Which table T1 is. If the date columns are produced by the Pivot Table macro, the Transformer has to wrap the pivot, not sit inside it. Otherwise T1 is your source data, and there is no "Jun 15,2026" column there to drop.

What "empty" really is in your pivot. The check relies on SUM returning NULL, which only happens when every cell in the column is null. If the pivot fills gaps with 0, that column will never qualify.

The names are dates. A script with 69 hardcoded column names stops matching as soon as the period moves. If your range shifts every month, filtering out the rows with no value before the pivot (so those dates never become columns in the first place) is usually easier to keep alive than dropping columns afterwards.

Disclosure: I build small Confluence apps, none of them related to Table Transformer or pivots, so no stake in which route you take.

Mia Tamm _Simpleasyty_

Hi @Teresa J Felder ,

Quick update on this. The functionality we mentioned above is now live in production in the latest version of Simple Tables for Confluence.

You can now automatically hide columns where all values are empty, which should make those large pivot tables much easier to work with without having to manually remove columns.

Thanks again for sharing the use case. It was really useful to have a real-world example while working on this!

Hope it helps!
Mia

Teresa J Felder

Thanks for the detailed information. The brackets did not work. I got an error it could not remove the column as it could not find the date column in T1. When i use the quotes I did not get this error. So i think it is seeing the columns just not actioning as expected.

Teresa J Felder

Mia,

Thank you for this information. It looks like it would be helpful to some. However, I think I have more complexity than this handles since I am using a Table Excerpt Include to pull the data from another Confluence page that is put into the pivot. Also, our Cloud version does not support Simple Tables. I cannot add new tools to Confluence on my own, and my company does not add new stuff quickly, so even if this is added it is probably not going to be soon enough to help me.

Mihai_LeanZero

That error only says alasql found no column of that name in T1. A header one character off does it (trailing space, non-breaking space, a space after the comma, the pivot printing the date differently), and so does a table the app loaded as plain rows, where even the exact name fails. Tested in plain alasql only, not in the macro.

No error with quotes doesn't show the column was found. In double quotes SUM("Jun 15,2026") is a string constant per their docs, null for any name, so the IF passes whatever the name.

List the real headers first, stiltsoft's docs have the query:

SEARCH / AS @TABLE1 RETURN (@TABLE1) AS @TABLE2 FROM T1; SELECT '0' AS 'All Columns' FROM @TABLE1

Copy the header from that output into the brackets. Don't type it.

Check T1 is the pivot output too. Transformer goes around the pivot, excerpt include stays inside it, and on Cloud their docs say a chain that deep needs the Table Toolbox macro.

Exact name still fails? Then ALTER on T1 probably won't work in there, and the fallback is filtering the empty dates out before the pivot.

Mikhail “in Peru”

@Teresa J Felder sorry for the slow follow-up. That error is actually good news: it means the brackets worked and the name didn't. I reproduced it in plain AlaSQL. With a header of "Jun 15,2026" and the script asking for [Jun 15, 2026], one extra space after the comma, you get exactly "Cannot drop column ... because it was not found in the table T1". With the name copied exactly, the same statement drops the column.

So Mihai's step is the right next one: list the real headers with the query he posted, copy one header from that output into the brackets, and try a single column first.

The quotes not throwing an error doesn't mean they worked. In SUM("Jun 15,2026") the name is just a piece of text, so the empty check passes for any name, and the drop never touches a real column.

If the exact name still fails inside the macro, then ALTER on T1 isn't supported there, and filtering the empty dates out before the pivot is the route that needs no new app, which matters since you can't install one.

Mia Tamm _Simpleasyty_

Hi @Teresa J Felder,

Thanks for explaining your setup in more detail! That makes perfect sense, especially in larger organizations where introducing new Marketplace apps isn't always straightforward.

I can see why this is frustrating. You're trying to solve a relatively simple problem, but the combination of Table Excerpt Include, Pivot Table, and Table Transformer makes it much more complicated than it should be.

Given your restrictions, I think the best approach is to focus on what you can achieve with the tools you already have.

One thing I'm curious about: are those empty date columns always empty in the original data, or do they only become empty after the pivot transformation?

That distinction might help identify whether you can filter them out before generating the pivot, rather than trying to remove them afterwards.

And thank you again for sharing the details. These kinds of real-world constraints are incredibly valuable for those of us building Confluence apps.

Hope you find a workable solution!

Mia

Teresa Felder

The ultimate issue was my not fully understanding the Pivot Macro. A field "Date period aggregation" was defaulted to "Day" which added in all the extra columns I did not need. And since there was no informational support I did not realize the purpose of the field. Resetting to "None" resolved my issue. I also chatted with the company that owns the marcro to add in the information detail so, in the future, others could also better understand the purpose of the feld.

To your question on the date columns. The dates in my source are not all separate columns. They are driven off of one column with dates/values set in each row that are then used to derive counts on the pivot for all rows for a given date or text string.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud