John Liu Blog

Every drop counts

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

Power Automate retry policy interval setting

The Power Automate retry policy interval setting follow the ISO 8601 date format for durations. The ISO 8601 durations are expressed using following format, where (n) is replaced by the value for each of the date and time elements that follows the (n). P(n)Y(n)M(n)DT(n)H(n)M(n)S P is the duration designator (for period) placed at the start of the duration representation. Y is the year designator that follows the value for the number of calendar years.

Posted by John Liu Wednesday, February 28, 2024