How to add a dynamic range on create table

[ajithd6368]

Hi Experts ,

below flow will format the excel sheet to a table  for another flow to upload it to a Sharepoint List. But the issue here is , the excel will not always having same number of rows (having same number of columns)  .. some days 3000 rows and next day it will be 200 rows in the excel file .

How do we configure the Table range to get the number of rows in the excel file and change the range dynamically .

rgds

AD

[Sundeep_Malik]

Hey @ajithd6368 

So I just did a test.

I have used a compose action for json array, the data I will insert in excel.

Then I used create table. In create table I gave A1:B1.

Then I used apply to each and inside it wrote add row into table action:

Final Result was like this:

So, I dont think table range is that necessary. You can just give A1:B1 and it will just add more rows at the end.

I added this before in Create Table action to get the length of compose action:

length(outputs('Compose'))

But for this the output came like this:

So I think better option would be to just use A1:B1 in table range.

I hope this helps 🙂

[ajithd6368]

Hi Sundeep,

Thanks for the response ..

In my scenario , excel sheet is already in a specific folder . Every day morning i get a new  file and number of rows always varies .  I just need to convert  it  to  a table  .. In your scenario you have a json to create data .. but i already have data in a file .

Would appreciate your advice here .

rgds

AD 

[ajithd6368]

Hi Sundeep ,

I would appreciate your advice  on my concern 

rgds

AD

[Sundeep_Malik]

@ajithd6368 

I tried a few things but they didn't work.

@takolota do you have any solutions for this?

[takolota]

@ajithd6368 

The most common solution I see for this is to use an office script like this: https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.table?view=o...

/**
 * This script creates a table from the current sheet's used range.
 * It then adds a total row to the table with the SUM of the last column.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the used range of the current worksheet.
    const sheet = workbook.getActiveWorksheet();
    const range = sheet.getUsedRange();

    // Create a table that has headers from that range.
    const table = sheet.addTable(range, true);

    // Have the table display the SUM for the last column.
    table.setShowTotals(true);
    const lastColumn = table.getColumn(table.getColumns().length);
    lastColumn.getTotalRowRange().setFormula(`=SUBTOTAL(109,[${lastColumn.getName()}])`);
}

The only other option I can think of is converting the Excel to a CSV & then using that CSV to create a new table. But that seems like a less direct/more complicated way.

[Chriddle]

Spanning the table over max rows (3000) and filter out the empty rows should work.

[ajithd6368]

Hi Chriddle ,

sorry for late response , i was travelling .. Seems this is the easiest  way .. Would you be able to guide me how to find the empty records .

rgds

AD

[ajithd6368]

Hi Chriddle,

I managed to create a filter as below flow

only issue here the column name i am using having a space in-between the name .. How can we ignore the space .. I tried many way ..eg within  single quote , double quote .. but giving me an error ..

If i use a column having  one word in it ,will work perfectly ..pls advice

rgds

AD

[Chriddle]

Seems that Excel's List rows action can not filter column headers with spaces.

Just geht all rows and use an extra Filter action.

[ajithd6368]

Thanks , i managed to get another field having no spaces in-between  ..