Converting a data range into a table

(0) Share

Report

Posted on by Shopps

Hi,

I have setup a daily report that is emailed from Service Now to me every week with a list of open tickets. This report can either be a .CSV or .XLSX format. I have so far created a Power Automate flow so that when this report is emailed to me then the attachment is automatically saved in a specified SharePoint location. so far so good.

What I want to do is to automatically convert this report from the normal data range that Service Now send out into a table once it is in SharePoint. The number & names of the columns will always be the same, but the number of rows will be changing every day of course.

I have seen a tutorial where it seems the key part is in the Create Table operation, the file name needs to be listed, and the tutorial shows that I need a dynamic function of 'File Name with extension' I may be going mad but for the life of me I cannot see this listed anywhere to select, therefore the flow is always failing.

Does anyone know why this dynamic function is not available, and also is there an alternative if the function is not listed?

Thanks in advance.

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

Meekou 200 Moderator on at

Like (0)

Report

Could you share more details about your requirement?

What do you mean by convert report from normal data range into a table?

For Create Table operation, do you mean create table in Excel?

Based on your current description, it seems you already upload a .csv or .xlsx file to sharepoint, and then want to convert this file to table data.

Do you need this table data during Power Automate or want to save them into SharePoint list?

Best Regards,

Meekou

Private Support

Was this reply helpful? Yes No
Suggested answer

Gopala_Krishna 1,495 on at

Like (0)

Report

As there is no information on the actions that are utilized in the flow, I am assuming that the flow is structured the following way.

1. When an email arrives

2. Get attachment from the email

3. Create file to SharePoint with attachment content

After the create file action you need to add another action "Get file properties" and select ID from the create file action which will be as follows

As you configure the "Get file properties" action with the required information, you would then be able to see the "File name with extension" option as part of the outputs of "Get file properties" action which you can use in the Create table

Overall flow setup for file creation, fetching metadata and creating table will look as follows for the actions

If the information shared helps you, kindly consider giving a like ❤️ and mark the response as resolved

Was this reply helpful? Yes No
Shopps 10 on at

Like (0)

Report

Hi,

Thanks for the replies so far. What I currently have configured is -

The report that comes in from Service Now can either be a .CSV or .XLSX format, i have tried both but there is no difference.

Do I have the Table Range formula correct? if the report is in the XLSX format, then the default name of the tab is Page 1 so I have =OFFSET('Page1'!A1,0,0,SUBTOTAL(103,'Page1'!$A:$A),8) .

The error message I see are -

I feel I am missing something obvious, but I cannot see what.

Was this reply helpful? Yes No
Suggested answer

Meekou 200 Moderator on at

Like (1)

Report

Based on your screenshot, it seems you want to create table on the new file under sharepoint.

If so, you are using the wrong parameter for File parameter.

Try to use DriveItemId instead of file name with extension.

Regards,

Meekou

Private Support

Was this reply helpful? Yes No
Suggested answer

Gopala_Krishna 1,495 on at

Like (0)

Report

@Shopps

You don't need to mention "/" at the file name you can directly select the File name with extension from the dynamic content with out the need for "/"

Was this reply helpful? Yes No
Suggested answer

Shopps 10 on at

Like (0)

Report

Thanks for responses.

This finally works!

I removed the / . Thank you Gopala Krishna.

And I also changed the File name with extension to Drive Item ID.

This did the trick,

Thank you for your quick and helpful support. 😊

Was this reply helpful? Yes No