Unanswered

Count Rows with irregular/missing data

(0) Share

Report

Posted on by Socrates

Hello

I've spent most of the day looking around the forums to try and find a solution to my problem and I just can't quite apply any of the solutions to mine.

I have an excel file arrive daily in an email attachment which is then saved to SharePoint. I have a Flow that turns the data inside to a proper table. I now need to do a simple count, but the data format isn't amenable as it is shaped like:

Header 1	Header 2	Header 3	Header 4
Team 1	Email	08/02/2021	10s
		08/02/2021	4s
		08/02/2021	2s
		08/02/2021	3s
Team 2	Email	08/02/2021	6s
		08/02/2021	20s

etc. - (note: the Team 1 data could range from 0, i.e. just begins with Team 2, or up to a few dozen)

The descriptive data under Header 1 and 2 are only listed once despite applying to all rows before it gets to the next Team.

In this instance I would need to count how many 'Team 1' rows there are. I thought I would try and find the row number for 'Team 1' and then for 'Team 2' and subtract one from the other, but no joy there.

This doesn't have to be in the Flow that creates the table - it can be in an entirely new Flow - perhaps off a 'when a file is created or modified in a folder' trigger.

(To complicate matters a little, there are actually always 10 'junk' rows at the beginning of the table, but that's a known constant and I can/have dealt with that in other ways)

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Socrates

How about an output like

[
  "Rubbish:7",
  "Team 1:4",
  "Team 2:3",
  "Team 3:8",
  "Team 4:4",
  "Team 5:1",
  "Team 6:2"
]

When your excel sheet looks like the following:

Solution is in the next post.

Was this reply helpful? Yes No
Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Socrates

Hopefully the flow below and attached commentary on image makes sense. Create three variables and increment the count if a blank row, reset it to 1 if the row column1 begins with team. I initially called the team name random as I assumed from your description that you had several blank rows.

Was this reply helpful? Yes No
Socrates 64 on at

Like (1)

Report

Thank you @DamoBird365

I had to do something slightly different*, as for clarity whilst I had named my teams 'Team x' for this thread, in actuality they were all unique from one another.

*I set up some specific variables to hold the row count of each team, then did some more nested conditions in the 'Yes' path if CurrentTeam contained the string for the specific team, then set that variable to the row integer.

Then did a compose outside of the Apply to Each to subtract Team 1 from Team 2

Was this reply helpful? Yes No