parsing excel fields into batches of 50 as variables to use later in a flow

[JPey]

Hello,

• I have a statically named excel spreadsheet (TheTest.xlsx) hosted in OneDrive.
  • The sheet has an indeterminate amount of 10-digit numbers in the A and B columns.
  • There may or may not be data in the B column.  

A	B
1111111111	4444444444
2222222222
3333333333	5555555555
6666666666	7777777777

• The goal is to batch the A and B columns entries into groups of 50.
  • Each 10-digit number is delimited with a comma (,)
  • Ideally blanks don't count as part of the 50
  • Each 50 batch is a variable that I can call into later parts of the flow
    • Var1: 1111111111,2222222222,...
    • Var2: 5555555555,6666666666,...
    • etc.

I am not sure where to ever start here. Any help would be appreciated! Thanks for your insights. 

[v-yueyun-msft]

Hi , @JPey 

According to your description, do you mean you want to get the values in columns A and B, and then group them up for every 5 values, separated by commas , like this:?

This is my test flow:

(1)The List rows and initial variable action:

(2)The Apply to each action:

(3)The last two actions:

chunk(body('Filter_array'),5)

For this , you can refer to :
Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[JPey]

@v-yueyun-msft You are close to what I am asking about.

I put a small example, but I really mean 50. The total between the A and B columns is approximately 5,000 numbers, I need them batched by 50 count. The problem is I will not know how many variables I will need due to the total (A+B) changing. 

Also, I need to eliminate duplicate numbers that may exist in (A+B). 

Any ideas? 

[v-yueyun-msft]

Hi , @JPey 

For the List rows in a table action, we can open the Pagination to get your 5000 rows in your table.

And we do not need to know how many the variables we need to batched by 50 count. The chunk() function can directly group the number  batched by 50 count.

And you want to duplicate numbers that may exist in (A+B).

We can just use the union() function to  duplicate numbers.

Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[JPey]

The total number of rows is unknown. Right now it is about 5000, but it will grow. 

I need to:

• Combine A and B column entries into one list
  • Each value is comma separated (1111111111,2222222222,3333333333, etc.)
  • Eliminate blanks that may be in the B column
  • Batch the list into groups of 50 for an HTTP request I am sending via JSON
  • Loop until all the batches of 50 are sent out using the HTTPS

[v-yueyun-msft]

Hi , @JPey 

Yes , the Paigination now is enough for your data . And for more information, you can refer to :
Retrieve More Than The Default Number Of Rows Of Data From Excel Using Power Automate (c-sharpcorner...

And if your data is very very big , you can just create a folder and separate your data to more than one Excel file. 

And we can remove the duplicate value like this:

union(variables('Data'),variables('Data'))

And the result is as follows:(in my test side , it groups of 5 items)

We can just Apply to each this Array , and then we can use each item() in your http action.

The first item() is "11111,22222,11112,22223,11113".

And this action is used to filter the blank value :

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[JPey]

I will this this and report back to you. Thank you for your help. I appreciate it. 

[JPey]

@v-yueyun-msft when I try to use LIST ROWS PRESENT IN A TABLE I cannot, because I do not have a table. So I added a step to create a table. I can CREATE TABLE (Table1), but I cannot move to LIST ROWS PRESENT IN A TABLE because Table1 doesn't exist yet. Any ideas? 

[v-yueyun-msft · Accepted solution]

Hi,  @JPey 

Thanks for your quick response and sorry for my delay response due to weekend.

If you do not have a table in Excel . One way is you can make it a table manually.

 

Secondly , you need to use the run Office Script to create a table in Your Excel file because this action need to have a table in our Excel File.

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  let newTable = workbook.addTable(selectedSheet.getUsedRange(), true);

}

Best Regards,

Yueyun Zhang

@JPey