MS Forms, Flow, Excel - How to stop Flow overwriting Excel row if MS Form question input is blank.

[csantab3]

Hello All, 

So to keep it really simple:

(1) I have an Excel sheet with 3 tables that feed into a PowerBI (this bit is fine so we'll ignore it).

(2) I have an MS form with all the same column headings and 3 sections, all corresponding with the Excel. 

I have created a Power Automate Flow (3) that uses the submissions to the MS Form (2) to update the rows in the Excel for each of the 3 defined tables (1). 

This all works. However, if any of the questions are left blank in the MS Form (2), then the Flow (3) overwrites the information in the cells previously inputted into the Excel (1) leaving it blank.

Thus, I need a way for the Flow (3) to ignore any blanks submitted in the MS form (2) and not overwrite the information in the corresponding row in the Excel (1). 

Does anyone have any ideas?

Thanks in advance for any support

[jorgeroque]

Hello  @csantab3 

I have reproduced your flow and have worked around your issue using an "if empty" expression.

Actions to take:

1) Create a Get a row action prior to your Update a row action: this way you save your previous data so you can use it in case a Form value is null.

2) Use an if(empty(...), ... , ...) expression using the expression box in order to account for cases where Form values are returned empty.

Logic: If the input value that comes from the form is empty, then return the previous value (get a row content), if not then use the form value.

Expression:

if(empty(Get response details dynamic content),Get a row dynamic content,Get response details dynamic content)

If this answer has helped you, please consider marking it as the Solution to help others finding it in case there are similar problems.

[csantab3]

Hello, 

I have tried this for all 100 questions on the MS Form, but upon first run, i got this - 

This is how I've built each - 

Any thoughts?

Thank you for your message, support, and time. 

All the best.

[jorgeroque · Accepted solution]

Hello @csantab3 

I did a troubleshoot and tried different scenarios to understand what might be causing your error.

- the Get_a_row_(Information_Sheet) renaming was not the problem;
- although the expression shown in the image above is correct, your error message is consistent with a case of misfilled expression, meaning somewhere in your 100 parameters there could be one, or more, misfilled expressions.

The process to solve this might be time consuming, nonetheless what I recommend you to do is to use a Compose action after Get a row and in the input parameter plug in the if(empty) expression (one at a time) to test and see if the return value for the compose is correct or not.

You can perform this by simply doing a copy + paste (see image below for reference).

[csantab3]

Okay again, thank your time and effort, you are awesome. 

So, I got all the If statements and pasted into a word document, there are 100 - 

), 

],

'Get_a_row'

and 200 -

'Get_response_details'

So numerically all the If statements are correct now, I found the problem with the first Update Row, i had missed a condition on the third IF down. 

I checked the rest of the statements one by one. 

Now I've got another problem - 

Thank you again for any further assistance. 

All the best.

[jorgeroque]

Hello again @csantab3 

You may be facing a similar problem I've come across during my research and the following suggestions might bring you to a solution (see link for reference).

Solved: WorkflowOperationParametersRuntimeMissingValue. Th... - Power Platform Community (microsoft....

1) Could you please check your excel file rows for an "id" column with an empty value or strange characters?

2) You can also try recreating the excel table from scratch.

Hope this brings your problems to a happy solution.

Best regards!

[csantab3]

Okay so I got to it work once but now its all broken. 

Few different issues: 

After this, I followed this post - https://powerusers.microsoft.com/t5/Building-Flows/WorkflowOperationParametersRuntimeMissingValue-Th...

I cleared my cache and which has resolved the fail to connect issue, but now its giving this error....

Its also not letting me see all the code I wrote...

Any further further further help is appreciated.

[csantab3]

I somehow worked this out, there was loads of tiny things wrong but there all resolved now thank you so much. Have a great weekend.

All the best.