Excel Update Row/ delete Row

[mmatusche]

Helle Everybody,

I have issues Updating and deleting rows in Excel.

I have build a Powerapp, which is based on an Excelsheet which holds the data. (I know there are probably better solutions for that but excel is the only thing i have acces to so far)The App is for me an my colleagues to share Hotel rooms which are already booked but not needed anymore (happens a lot in our company)

They can enter the dates where they have booked the Hotel so others can see and take their room. 

What i want to do now with Flow is following: I want to Check wether the current date is the same than the begin date from the booking, if yes delete the entry if no do nothing. In this way there will not be outdatet entries in the app.

From theory this looks pretty easy but i cannot manage to implement it in Flow.

First step is a Timer which starts once a day to update the date in the Table. First question: I can only select "Update row"("Zeile aktualisieren" on the screenshot) not "Update Column" Why is that? In my case the date is in a seperate column is there a workaround?

Do i have to type in anything in one of the fields below? What i want Flow to do is simply fake me open the table and close it again. In one column the is the formula Today() so there should be the current date after it has been updatet.

If i skip that problem i come to another one:

 The condition says If the current date is equal to the beginning date (of the booking) then delete a row otherwise do nothing. I have no idea what to put in the fiel ROW -ID ("Zeilen-ID" in the Screenshot) This time i can pick dynamic content, but the choices i get are my column headers. Why can i only pick column headers if i want to delete a row? And how do i define to delete the row in which the condition is fulfilled and not the others?

I feel pretty sorry for asking this stupid questions but i have worked myself through the guided learning and i want to use Flow but i dont know how to continue.

Thanks in advance for any Help

Manu

[v-micsh-msft · Accepted solution]

Hi @mmatusche,

It's OK. I will try to answer the questions you encountered.

First, why the Update Row, not Update column.

That is because Microsoft Flow "Marked" the data stored in the Excel table in the Row format, you should notice there would be "_PowerAppID_" column auto-generated once Microsoft Flow has accessed the Excel file, each row would have a unique ID. That is the ID which would allow Microsoft Flow to find the proper data.

Second, how to make the flow work.

To update the whole table, we may take use of the Get Rows action after the Recurrence, then add an Apply to each, for each row, check to see if the date less than or equals with today.

Here I use the Dynamic content from the Get Rows for the condition Object Name part,, for the value, I used the UTCNow() function.

Microsoft Flow would work with Workflow Definition Language.

See my screenshots:

And the results:

Please follow the screenshots and see it it would work.

if you have any further questions, please post back.

Regards

Michael

[mmatusche]

Hey Michael

thank you very much! I think i got your ídea how to solve my problem. Your second step is clear, but what did you do in the first step? Im mean in the "get rows" step. I selected the Excel sheet, the table and tried to insert the Row ID but in any case i do not get dynamic content in the "select output from previous steps" where is my mistake?

I think the rest is clear, thank you again. Especially for the hint to the Workflow definition Language.

Greetings

Manu

[v-micsh-msft]

Hi @mmatusche,

Please add the Excel Get Rows Action after the Recurrence.

Follow the steps below:

1. Add the Get Rows Action, where you only need to specify the file name and table name,

2. Add a condition directly, then specify the Date column in the object Name field, it will then auto add the Apply to each,

3. Then follow the steps I mentioned above, make sure the condition is set to is less than or equal to

4. under the If yes part, there should be Row Id content available.

Regards

[mmatusche]

Ah i see, there is a singular "get row" and a plural "get rowS" function.

Thank you everything works! I have learned a lot and i relly really apreciate your help!