John Liu Blog

Every drop counts

Power Automate with Excel

When we work with Excel in Power Automate, we might need to remove the filter on worksheet to be able to create a data table. If filter is enabled, Excel Online for Business connector might not be able to create a data table. We can create Office Script within Excel Online for Business. To save the script so Power Automate can use it, we must open the file in Excel Online (the web version).

Posted by John Liu Friday, February 13, 2026

Power Automate Remove Elements from JSON Array

In Power Automate, when processing JSON array, you might need to remove unwanted elements from the JSON array. When you read an Excel worksheet from OneDrive or SharePoint, the result is a JSON array and two internal elements (@odata.etag and ItemInternalId) will be added into the result JSON array. To remove the @odata.etag and ItemInternalId from the result array, add a Select data operation action. Set the From value to be expression:

Posted by John Liu Monday, July 29, 2024

Excel Tips

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:

Posted by John Liu Saturday, July 27, 2024

Power Automate notes

Add newline to string variable using concat There are several ways to Add a newline when appending to string variable: Add ‘Enter’ into the expression. concat(‘Line1’,’ ‘,‘Line2’,’ ‘,‘Line3’ ) Create a ‘NewLine’ variable and in the ‘Value’ field press Enter to assign line return to it. Use this variable in the concatenation. Use decodeUriComponent(‘%0A’) expression concat(‘Line1’,decodeUriComponent(’%0A’),‘Line2’,decodeUriComponent(’%0A’),‘Line3’) Remove newline from string There seems no easy way to remove newline/line break from multiline string.

Posted by John Liu Tuesday, March 26, 2024

Power Automate with Snowflake

Configuration There is a Snowflake connector in Power Platform to connecto to Snowflake database using Snowflake API calls. To be able to use the Snowflake connector, Azure AD (Entra ID) authentication for Snowflake needs to be setup on Snowflake side. Follow Getting started with Power Apps and Snowflake on how to configure the Azure AD authentication for Snowflake. timestamp value In Snowflake Power Platform connector, the timestamp_ntz data type represents a timestamp in UTC time zone without time zone offset information.

Posted by John Liu Tuesday, March 26, 2024