Extracting Headers from an Excel Table using Power Automate

[hitakshi]

Hello community,

I hope this message finds you well. I am currently working on a project where I need to extract the headers from an Excel table that contains 30 columns. I've been exploring Power Automate, and I'm wondering if anyone could kindly guide me through the process.

Specifically, I would like to understand how to extract only the headers from the table using Power Automate. If anyone has experience with this or can provide step-by-step instructions or insights, I would greatly appreciate your assistance.

Thank you in advance for your time and expertise!

Hitakshi

@automate @PowerAutomate @pow1 @powerautomater1  @PPower @yipowerautomate @powerautomate88 @powerautomate17 @HAutomate @Excel @SharePoint @Anonymous @v-yetonggu-msft 

[DarmonGill]

Hi,

One way to extract the headers from an Excel table is to use the “Excel - Get rows” action to retrieve the rows from the table and then use the “Compose” action to extract the values that you need from the rows. Here are the steps:

1. Add the “Excel - Get rows” action to your flow.
2. In the “Get rows” action, select the Excel file and worksheet that contains the table.
3. In the “Get rows” action, set the “Top count” to 1. This will ensure that only the first row of the table is retrieved.
4. Add a “Compose” action to your flow.
5. In the “Compose” action, use the following expression to extract the headers from the first row of the table:

outputs('Get_rows')?['body/value'][0]

Gill 

[hitakshi]

Hi @DarmonGill 

I followed the steps you provided, but I'm unsure about what to input in the "Key Column" and "Key Value" fields for the Get Row action.

[v-yetonggu-msft · Accepted solution]

Hi @hitakshi ,
I did a test for your reference.

In my scenario:

split(string(first(outputs('List_rows_present_in_a_table')?['body/value'])),'",')
split(replace(replace(replace(item(), '"', ''), '}', ''), '{', ''), ':')[0]
skip(body('Select'),2)

My Excel Table:

Best Regards,

Sunshine Gu

[hitakshi]

Hi @v-yetonggu-msft 

Thank you so much for your solution. I followed the steps you provided, and it's really working for me. It's truly helpful😊

Thanks,

Hitakshi

[hitakshi]

Hi @v-yetonggu-msft 
In the "Select" action, I receive output like this. Can you suggest how I can sort this out?

I used the code you provided in the previous step.

Spoiler

[

  {

    "Date": ""

  },

  {

    "Invoice Date": ""

  },

  {

    "Invoice ID": ""

  },

  {

    "Customer ID": ""

  },

  {

    "Customer tax ID": ""

  },

  {

    "Customer name": ""

  },

  {

    "Customer address": ""

  },

  {

    "Customer address recipient": ""

  },

  {

    "Billing address recipient": ""

  },

  {

    "Billing address": ""

  },

  {

    "Due date ": ""

  },

  {

    "Payment terms": ""

  },

  {

    "Invoice amount": ""

  },

  {

    "Amount due": ""

  },

  {

    "Purchase order": ""

  },

  {

    "Previous unpaid balance ": ""

  },

  {

    "Remittance address": ""

  },

  {

    "Remittance address recipient": ""

  },

  {

    "Service address": ""

  },

  {

    "Service address recipient": ""

  },

  {

    "Service start date": ""

  },

  {

    "Service end date": ""

  },

  {

    "Shipping address": ""

  },

  {

    "Shipping address recipient": ""

  },

  {

    "Subtotal": ""

  },

  {

    "Total tax": ""

  },

  {

    "Vendor name": ""

  },

  {

    "Vendor tax ID": ""

  },

  {

    "Vendor address": ""

  },

  {

    "Vendor address recipient": ""

  }

]

[v-yetonggu-msft]

Hi @hitakshi ,

Clear the contents of the Map.

Best Regards,

Sunshine Gu

[v-yetonggu-msft]

Nothing, I'm just clearing my duplicates.

[hitakshi]

Hi @v-yetonggu-msft 

Thanks a lot😊, It's really working.

[hitakshi]

Hi @v-yetonggu-msft 
Your solution works when there are values in the rows, but if there are no values in the row, we are unable to retrieve the table headers.
Do you give another solution?

[khushikashyap12]

Hi,

How do I dynamically extract table headers if my table contains zero rows? The previous solutions didn't work in this case but it works well even if there is a single row but here it's showing an empty string as the table header. Is there a way to achieve this in Power Automate?