I've divided this into 2 seperate problems:
Get the data and Do the calculation.
Get the data
Getting more than 300000 rows from an Excel file takes some effort.
For simplicity I assume that you can do a CSV export.
1. Get the CSV's file content
My CSV file looks like this (with 300000 lines):
2. Filter for not empty lines:
Normalize the line breaks, split at line break and skip the first line.
From:
skip(
split(
replace(
body('Get_file_content'),
decodeUriComponent('%0D%0A'),
decodeUriComponent('%0A')
),
decodeUriComponent('%0A')
),
1
)
Filter: @item() is not equal to @string(null)
3. Create the data array with a Select
From: @{body('Filter_array')}
Map Country: @split(item(),',')[0]
Map SBU: @split(item(),',')[1]
Map Grade: @split(item(),',')[2]
Map Status: @split(item(),',')[3]
Do the calculation
1. Filter array-GradeB
We only need to process items with Grade B
From: @body('Select')
Filter: @item()['Grade'] is equal to B
2. Compose-XML
Convert the array of objects to XML for later xpath queries
Input:
xml(json(concat('{"Root":{"Item":', body('Filter_array-GradeB'), '}}')))
3. Select-Index
Extract County and SBU from the objects
From: @{body('Filter_array-GradeB')}
Map Country: @item()['Country']
Map SBU: @item()['SBU']
4. Select-GradeB
Count the items
From:
union(body('Select-Index'), json('[]'))
Map Country: @item()['Country']
Map SBU: @item()['SBU']
Map Grade: B
Map Status:
xpath(
outputs('Compose-XML'),
concat(
'count(//Item[',
'Country="', item()['Country'], '" and ',
'SBU="', item()['SBU'], '" and ',
'Grade="B" and ',
'Status="Completed"',
'])'
)
)
Map Total:
xpath(
outputs('Compose-XML'),
concat(
'count(//Item[',
'Country="', item()['Country'], '" and ',
'SBU="', item()['SBU'], '" and ',
'Grade="B"',
'])'
)
)
5. Select-GradeBWithPercent
Add percentage
From: @{body('Select-GradeB')}
Map:
addProperty(
item(),
'Percent',
formatNumber(
div(
float(item()['Status']),
int(item()['Total'])
),
'P',
'en-us'
)
)
The output of this last Select looks like the following
[
{
"Country": "DE",
"SBU": "DD",
"Grade": "B",
"Status": 2491,
"Total": 12193
},
{
"Country": "US",
"SBU": "DD",
"Grade": "B",
"Status": 2330,
"Total": 11888
},
{
"Country": "UK",
"SBU": "BB",
"Grade": "B",
"Status": 2449,
"Total": 11805
},
{
"Country": "DE",
"SBU": "EE",
"Grade": "B",
"Status": 2288,
"Total": 11964
},
... 16 more items
]
and it takes 2 to 3 minutes to get there
