Using Files Dynamically and Still Get to Data

[bmbyars]

I have a flow built that takes a file and copies it with a new file name. The initial file is called hostProgramData and the copy will be hostProgramData1 where the 1 will built up incrementally. I am then using the SharePoint "get file properties" to get the location and name of the most recent file. I am using this information throughout the rest of the process. This file is an excel sheet and I need to add a table name, column and then update the rows with data brought back from a Bing maps API. This is all working when I specify an actual file that exists (like in the photo below) 

However when I just use the sharepoint file path (as I assume I would have to since the file name changes each time this is ran) this is what I get, all of the column headings from the file are gone. 

Is there a way to make this work? I have tried parsing the JSON from the excel file after I used the "List rows present in table" without any luck. If there is a better way to going about this, please let me know. Or if there isn't please give me some advice on how to do this correctly. I appreciate your help!

[ArchitectMadhan]

@bmbyars , Hope the below blog will help you to create the excel workbook with rows in it and are created dynamically. Filename can also be given dynamically.

https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Create-Excel-Workbook-in-SharePoin...

Please accept it as a solution and give thumbs up, If it works for you.

[bmbyars]

@ArchitectMadhan thank you for the response! This isn't really what I am after or I might just be looking at this wrong. Here is a breakdown of what I want to do and where the problem is:

- I have an exported Excel file that contains about 3000 rows of data about constituents that runs every night. This is a file that I want to copy and add data to it. So my first step is to copy this file to a new location within Sharepoint. 

- Since this job can be run multiple times back to back, I need to create a new file with a new name for each of these copied files. (here is the first issue as when I do this, I can no longer select a specific file)

- I then get the new files properties (mostly just the location of "full file path") so I can feed the file path to the following steps. I do this by using the "Get File Properties" action and use this in the ID:

  

**NOTE** all the below steps use this for the file as I cannot select an actual file because I would not know what the latest file name would be:

- Next I create a table in the new Excel sheet using the Create Table action.

- Then I put in an additional column for the data that I want to add, in this case it is a distance from that constituent to a designated location. 

- Then I read data from the Excel document using the "List rows present in a table" 

- I need to be able to use the data returned in the previous step in the next step so I then put the JSON in a variable and parse the JSON. This give me an opportunity to get to the data without needing to set an actual file in the next steps. 

- No I am going to reference the BING maps API to get the distances from the constituent to the given (using a form in Power Apps) address. The "destination" address is stored in variables already and the constituent address is reachable by the parsed JSON from the action above. 

THIS IS WHERE I AM RUNNING INTO ISSUES:

- After I get the data from BING I want to loop through that data and do one of two things, I want to add the distance of the constituent to the destination in the column added above OR If they are outside a certain distance away from the destination, I want to remove their row of data from the Excel sheet. I know that this is possible because I can get it to work when I add a specific file instead of using the "Full Path" . But when I get to the point where I am adding data to the excel sheet, I can't do it because it doesn't know what is in that file until the process runs due to the fact that I am providing a "full path" instead of an actual file. Here is the difference between what I see in the given file and the full path

given file:

Full path:

Now all of the options that I have are gone. Is there not a way to update a row in an excel sheet without giving it an exact file? This is really what I tripping me up. As I stated in the beginning, I am more than happy to go a better direction if needed but I can't think of another way to pull off what I am trying to do.