Perform Common Data Transformations Easily

While the default XLPublish setting is to publish current Snapshots of your spreadsheet results, you can easily convert any Tagged data set to collect a sequential set of data for the number of periods you define, simply and easily (Continuous Tables).

You may also merge the contents of multiple Ranges or Tables on subsequent worksheets by another simple setting in your XLPublish settings (Consolidation Tables)

You can convert the data inside Traditional Spreadsheet Reports into properly structured relational tables, arranging just the data from them into a table, with a series of embedded “keys” in your Worksheet (UnPivot Tables)

Or you can combine all of the above …. !

Continuous Tables… If you wish to have your data collected in sequence for every Publish (rather than as a Snapshot replacing previous data) you add 2 dots, “..”, and the number of periods you wish to collect this way by using the Edit Button.  For example if you add “..12” on the end of a table name XLPublish will collect 12 versions of the Tagged ranges data and the 13th time it will drop the 1st batch of data and will do the same for all subsequent refreshes.

Consolidation Tables … If you wish to collect certain identically structured data separately for different sources, you can place a set of these Worksheets in sequence in your Source Workbook (i.e. One Worksheet for each Region in you Organization).  To merge these together you add two underscore characters “__” at the end of the Table name and then the number of Worksheets that you wish to include this way by using the Edit Button. So to merge 4 Worksheets you would add “__4” to the end of your edited table name.  You then TAG the range or Table in the first Worksheet .. This feature is only available for Ranges of cells and Tables.

Continuous Consolidate Tables … You can do both by simply putting both “codes” at the end of your table name (i.e. “..12__4” will captured the values in the range tagged for 12 periods from4 contiguous Worksheets).

UnPivot Tables … With a few codes entered in cells the the right and below a range in your Source Workbook you can select the data you want to extract from a traditional Excel report (with Row and Column Headings, Totals and Subtotals, blank rows and Columns and even extraneous Text and formatting) and Publish it to your Target File as a true relational Table.

Here is a spreadsheet set up with examples of the 2 layouts we accommodate in XLPublish.


There is no comment on this post. Be the first one.

Leave a comment

%d bloggers like this: