How to tag data in a Source file

In the “Manage” Dialog you can select (“tag”) ranges and objects (pivot tables, charts) in your Source file.  Once Tagged, these items will be captured by XLPublish when you Publish, converted to Relational tables and pushed to whichever Target destination you chose (XLP Target File and/or an Azure/SQL Database or Power BI Service Dataset).

Add

The list above shows two “Containers” of data already Tagged in a Source file.

Tables

To select new ones from this dialog you click “Add” here and this dialog appears:

This dialog list all untagged Table objects in your Source file … You can click on any item, it will show you this item and you can click “Add” or simply double-click to Tag this table and add it to your list.

Pivot Tables

If you select “Pivot” on this dialog you will see all untagged Pivot Tables in your Source file:

Again you can “Add” this item or you may double-click it to “Tag” it.

Charts

If you click on “Chart” you will be shown a list of “Taggable” Charts in your Source file:

Again you can click “Add” or double-click the item to “Tag” it.

When you “Tag” a Chart just the data behind the chart will be converted to a Relational Table design so you can recreate the same Chart in Power BI using the many Visualizations available there.

Ranges

When you select “Range” on the Dialog:

You will be prompted to select the Range of cells you wish to “Tag”:

You then select the range you wish to “Tag”.  This range must be table-like with titles in the first row(s) and rows of “records” that you wish to Publish.

The simplest ranges look like Tables and you just select the range and click “Add” or double-click”.  There MUST be a blank cell in the row below and the column to the immediate right of the range you select as XLPublish will fill that cell with a custom formula that it needs to define the range you are selecting.  In this way you will be able to add or delete rows or columns within this range with the Tag staying active and accurate.

Once the range is “Tagged” you will be asked whether you wish to Auto-Tag the range for Unpivot…

If your range is simply ‘Table-like” and can be Published intact as it … the click No here.

If on the other hand this range is actually a “Traditional Spreadsheet Table”, with formulas for subtotals and totals, maybe with blank rows between rows or columns within the range or with extra text in the range that is not related to the data you wish to publish then you can select “Yes” here.  XLPublish will then do its best to estimate how you wish to transform your selected range and will place “codes” in the rows below and columns to the right of the range you just tagged … like this:

Once you click “Select” XLPublish will add codes around this range like this:

Notice the ‘#” characters … Those are indicating that any rows within the Tagged range that have a # in both the row and the column WILL be included in the Published Table.  You can of course choose NOT to let XLPublish auto-generate the codes here and you can add them manually.  After you’ve done a few of these you may find that the way you want to do this.

In this example the word “Date” above the “#” characters off the end of the title row indicates that the titles of each column in the range of values should be “Unpivoted” into a new column (field) in the Publishde table with a heading “Date” ..

This means that in the Target file to be published this range will be converted to this:

You should realize that once you have tagged a range to Unpivot you can click on the formula in the bottom-right corner of the tagged range to see a Preview of what you will get:

There are many more considerations in designing for an Unpivot that will allow you to accommodate more layout scenarios.  They are detailed in another blog at :

Delete

If you Delete a tagged object it will not be Published.  Also if this tagged range was previously published to the same Target file that table will be removed from the Target file.

Edit

Once you have tagged a container of data in this Source file you can then change the name of these tagged objects in the Edit dialog like this:

Continuous

By default XLPublish takes “Snapshots” of the data in the tagged ranges.  In this scenario you use your spreadsheet to consolidate the data you wish to Publish at that moment and Publish that data to replace the previous data as of a previous point in time (like a month end or like a previous week).  There of course will be scenarios where you might want to actually wish to collect a continuous string of Snapshots (such as every period for 2 years).  In order to accomplish this all you need to do is add two dots and a number indicating how many periods you wish capture at a time after the name of the tagged object.  Like this:

So in this example XLPublish will collect 24 versions of the tagged range into one Target table and when the 25th gets Published the first will be dropped to maintain a rolling 24 Snapshots.

In the Target file each Snapshot will be flagged with its incremental number.  We suggest you make sure the date of each snapshot is inside your tagged range.

Comment

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

Leave a comment

%d bloggers like this: