Just came up against a very interesting Power Automate problem that I had never come across before. I needed to be able to create an excel file and populate a different data set in a spreadsheet dependent on some criteria.
I thought it would be a matter, of creating an Excel template and then copying it over to a new file location, but not that simple.
I had to first create the Excel file, then get the content metadata and store that in a compose step. This gave me a blank Excel file content, which I could then save anywhere I wanted.
After that I created the file with that file content from the compose step, in SharePoint and gave it a name. Then use Create a table action:
Then, and this is the cool bit, I had to add a row into that table, because I didn't have any row information, I used JSON to create the row based on the values supplied and into the columns I had created. Using key value pairs from the manual trigger and the table column names:
Nice !!