Convert Excel File from outlook to Excel Table in sharepoint using Flow

[amorris2]

I'm trying to use flow to take an excel file from a delivered email, convert the file to an excel table and upload the new table to sharepoint. Below is the current flow that I have.  The current flow puts a regular excel file into sharepoint, but I need this file converted to a table.

Part 1Part 2

[v-lin-msft]

Hi @amorris2,

What do you mean by the table? Is it a SharePoint list?

If so, after you have converted the file received from email to an excel file, you could to add each row of the table in excel to the item in the SharePoint list by action create items of connector SharePoint.

If not, could you please tell me the specific table you refer to?

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amorris2]

@v-lin-msft,

Right now I just have the file going to sharepoint as a regular excel file, it is not a sharepoint list.  Basically my end game is to be able to use power query off of that data, and also to use the data for some power BI Dashboards.  If creating a sharepoint list (I also would need help with creating this flow too) is my best option, then I can do that as well.  I just know when I try to load the current file into power BI, it says it has to be in a table format.

[v-lin-msft]

Hi @amorris2,

So your question is when the file was imported into PowerBI something went wrong, right?

You can check whether there is a complete table in the excel file that you need to import. Since the excel file created in this way does not create a table, you need to create a table manually.

This is done by selecting all the data in excel, including column names, and then clicking the "Insert Table".

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amorris2]

@v-lin-msft,

Correct.  I'm getting a report out of a different BI system and the file is not in a table format.  I'm trying to automate the process without having to open the file, select data source, and then create table.  I want this to be done using Flow and have it also uploaded to my Sharepoint site using flow as well.

[v-lin-msft · Accepted solution]

Hi @amorris2,

Currently, there is no action could create table directly in the newly created excel file, so you need to manually open the file to create a table.

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ebolatkale]

I'm having the same issue, wanted to follow up whether anyone was able to find a solution to this

[djmitz6]

If you have Excel Online (Business) you can actually create an office script that will convert a range to a table.

After you update your file on Sharepoint you need to add a Excel Online (Business) "Run script" step.

To write the script you need to go into Excel Online and click on the Automate tab. Once there click "All Scripts" then in the right pane click "New Script". Enter the below code or modify as needed.

function main(workbook: ExcelScript.Workbook) {
  //Sets the Active Worksheet to the first worksheet in the workbook
  let selectedSheet = workbook.getWorksheets()[0];
  //Get used range of previously mentioned worksheet 
  let usedRange = selectedSheet.getUsedRange()
  // Add a new table
  let newTable = workbook.addTable(usedRange, true);
  return newTable.getId();
}