Power Platform Community Forum Thread Details

I'm trying to use Write to Excel Worksheet -> Value to Write ->

=CONCATENATE("DELETE ",TEXTAFTER(CELL("filename",$A$1),"]")," WHERE ",$A$1," = '",INDIRECT("A" & ROW()), "' AND ",$DB$1,"= '",INDIRECT("B" & ROW()), "' ;")

same for

=CONCATENATE("INSERT INTO ",TEXTAFTER(CELL("filename",$A$1),"]")," (" & TEXTJOIN(", ",TRUE,$A$1:OFFSET(INDIRECT(ADDRESS(1, COLUMN())),0,-3)) & ") ",SUBSTITUTE(" VALUES ('" & TEXTJOIN("', '",FALSE,INDIRECT("A" & ROW()):OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-3)) & "');","'NULL'","NULL"))

but I received this error:

Unexpected characters. Characters are used in the formula in an unexpected way. Expected operator. We expect an operator such as +, *, or & at this point in the formula.

Please advise!

Categories:

Power Automate Desktop

One way of doing it is to just create that action in regular flow and copy paste it into the Power Fx flow. But you cant edit that action in the power fx flow or it will give errors.

Or you could create a text file where you have that function already written and just use "Read text from file" and use that output to write to excel.

Or you can use scripting like Run .NET Script to create text value that you can then write to excel.

Here is the action to copy and paste to your flow:

Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $fx'FormulaString = @"=CONCATENATE(""DELETE "",TEXTAFTER(CELL(""filename"",$A$1),""]""),"" WHERE "",""$A$1"","" = \'"",INDIRECT(""A"" & ROW()), ""\' AND "",""$DB$1"",""= \'"",INDIRECT(""B"" & ROW()), ""\' ;"")";
' @'name:FormulaString': $fx'' @'type:FormulaString': $fx'String' @'direction:FormulaString': $fx'Out' @FormulaString=> FormulaOut

I would still recommend using regular Power Automate Desktop flows rather than Power Fx enabled flows since Power Fx has much more bugs in it still.