The Analytics Edge Core Add-in allows you to append the results of a query to the existing data in a worksheet. While the function is simple, there are some scenarios where you can experience problems. This article describes the problems you could experience and the solutions.
Dimensions That Look Like Numbers
The Append function looks for columns that are not numbers, and it uses all of those columns to try to align the rows of the new query with the existing data in your worksheet. If a column, like Week of Year or Month of Year (e.g. 201810) is not formatted as Text, then the Append function will ignore the value when it aligns the rows — the result: mashed up numbers.
Solution: make sure the existing column of data is formatted as ‘Text’ in Excel (not ‘General’).
Dates That Aren’t Dates
Sometimes you get data that looks like a date, but it is actually text. In Excel, a date is actually stored as a ‘serial number’ and formatted to appear as a date. In some Analytics Edge connectors, dates can be downloaded as text strings, and it can be difficult to tell them apart. IF you are appending data with one data type into a worksheet with the other, strange things can happen (extra columns, improper row alignment).
Solution: In Excel, select the worksheet cell and check the format – if it shows ‘Date’, then you are dealing with a date. If the query column is Text, the add-in will try to convert it into a date, and things should work as expected.
Solution: If the worksheet data is formatted as Text, and it is not in the 2018-10-31 format, Analytics Edge will likely be unable to align the rows properly — you may get duplicate rows with different date formats (actually text strings). Change the existing data to a Date in Excel (how it is displayed as not important), or change the text to 2018-10-31 representation to be consistent with the connectors.
