Date/Datetime
When using Power Automate to extract Excel data into CSV, the date value might be converted to a numeric value. That numeric value is the date difference between 30/DEC/1899 and the actual date value. If the numeric value is a integer, that represent a date (or time portion all 0s). If the numeric value is a float/decimal, that represent a datetime value.
So, if you need to convert that integer value back to it’s original date value in SQL:
SELECT DATEDIFF(DAY,'30/DEC/1899','1/MAY/2024')
,DATEADD(DAY,45413,'30/DEC/1899')
For datetime value, the numeric value will be a float:
SELECT
CAST(45413.875433912 - 2e AS DATETIME) AS ExcelToSql,
CAST(CAST('1/MAY/2024 21:00:37.490' AS DATETIME) + 2e AS FLOAT) AS SqlToExcel
Use the second method to always convert to datetime and then let SQL schema to do the implicite conversion to date if required.
SELECT
CAST(45413 - 2e AS DATETIME) AS ExcelToSql,
CAST(CAST('1/MAY/2024' AS DATETIME) + 2e AS FLOAT) AS SqlToExcel
In Power Automate, if you know the date column name and needs to use the date value, you can using following code to convert the value.
formatDateTime(
addDays(
'12-30-1899',
int(items('Apply_to_each')?['MyDateColumn']),
'MM-dd-yyyy'
),
'MM-dd-yyyy'
)
The true fix in Power Automate is to configure the DateTime Fomrat to be “ISO 8601” in the advanced options for “List rows present in a table” action. That way, the date value format will be preserved.
Scientific Notation
Numeric value might be converted into scientific notation format to the CSV file. This might cause issue with SQL Server as it can’t implicitly convert the scientific notated string value to numeric value. To properly handle this case, we need to convert the scientific notated string value to FLOAT or REAL before explicitly or implecitly convert it to the target numeric data type.
SELECT CONVERT(DECIMAL(18,3),CONVERT(FLOAT,'1.2E-5'))
OFFSET
OFFSET returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
Syntax: OFFSET(reference, rows, cols, [height], [width])
When trying to create a table in Excel Online in Power Automate, you can using following formula to specify the data range for the table:
=OFFSET(‘Summary’!A1,0,0,SUBTOTAL(103,‘Summary’!$A:$A),SUBTOTAL(103,‘Summary’!$A$1:$ZZ$1))
Here, SUBTOTAL(103,‘Summary’!$A:$A) returns the number of rows in columnA, and SUBTOTAL(103,‘Summary’!$A$1:$ZZ$1) returns number of columns that have value in row1, effectivly select the entire worksheet with data in a table format. Please note, if row1 has blank column in the middle, the selected range will be shorter as those blank cells are not counted result with the total be smaller.
If you need dynamic worksheet name, using following expression:
concat(’=OFFSET(’’’,variables(‘WorksheetName’),’’’!A1,0,0,SUBTOTAL(103,’’’,variables(‘WorksheetName’),’’’!$A:$A),SUBTOTAL(103,’’’,variables(‘WorksheetName’),’’’!A1:ZZ1))’)
Subtotal
Excel SUBTOTAL is not just totaling numbers in a defined range. It performs different operations suchs as counting cells, calculate average, get mininum or maximum values, etc.
Excel SUBTOTAL Syxtax:
SUBTOTAL(function_num, ref1, [ref2], …)
The function_num determine what actual calculation will be performed:
| Function_num | Function | Decription | |
|---|---|---|---|
| 1 | 101 | AVERAGE | Returns the average of numbers |
| 2 | 102 | COUNT | Counts cells that contain numeric values |
| 3 | 102 | COUNTA | Counts non-empty cells |
| 4 | 104 | MAX | Finds the largest value |
| 5 | 105 | MIN | Finds the smallest value |
| 6 | 106 | PRODUCT | Calculates the product of cells |
| 7 | 107 | STDEV | Calculates standard devision of a population based on a sample of numbers |
| 8 | 108 | STDEVP | Calculates standard devision based on entire population of numbers |
| 9 | 109 | SUM | Calculates sum of the numbers |
| 10 | 110 | VAR | Estimates the variance of a population based on a sample of numbers |
| 11 | 111 | VARP | Estimates the variance of a population based on entire population of numbers |
The difference between the function_num is:
1 - 11: ignore filtered out rows, but include manually hidden rows.
101 - 111: ignore all hidden rows, both filtered out and manually hidden rows.
Usage example:
Find last column# in the first row: SUBTOTAL(103,A1:ZZ1). This is same as SUBTOTAL(3,A1:ZZ1) as we are operating at column level.