cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmatusche
Regular Visitor

Excel Update Row/ delete Row

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.

Unbenannt2.PNG

 

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:Unbenannt1.PNG

 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

1 ACCEPTED SOLUTION

Accepted Solutions
v-micsh-msft
Community Support
Community Support

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:

21.PNG

 

And the results:

22.PNG

Please follow the screenshots and see it it would work.

if you have any further questions, please post back.

Regards

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-micsh-msft
Community Support
Community Support

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:

21.PNG

 

And the results:

22.PNG

Please follow the screenshots and see it it would work.

if you have any further questions, please post back.

Regards

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?Unbenannt3.PNG

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

 

Greetings

Manu

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.

25.PNG

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Top Solution Authors
Top Kudoed Authors
Users online (1,336)