Nick Burns
Member
- Joined
- May 24, 2017
- Messages
- 162
- Reaction score
- 0
- Points
- 16
- Excel Version(s)
- Office 365
I have a self-join table where I'm trying to find the number of Child Jobs to a Parent Job. I guess you can call this a "COUNTIF()".
I've done two approaches in my attached file:
1) Merge the table, expand, Group By all the original columns to get the count of rows
Pros: Fast - as in mere seconds
Cons: Although my sample only shows 5 columns, my actual data has about 25 columns and may expand in the future making modifications to the Group By step necessary
2) Merge the table, add a column that does a Table.RowCount() on the column and removes the merge data
Pros: alleviates the con in scenario 1
Cons: Slow - with my data this takes about 8 - 9 minutes.
Note: my datasource is a CSV file from our ERP system. The attached has sample data as a Table source.
Am I missing something obvious for this or is there a better approach to this?
Thanks,
Nick
I've done two approaches in my attached file:
1) Merge the table, expand, Group By all the original columns to get the count of rows
Pros: Fast - as in mere seconds
Cons: Although my sample only shows 5 columns, my actual data has about 25 columns and may expand in the future making modifications to the Group By step necessary
2) Merge the table, add a column that does a Table.RowCount() on the column and removes the merge data
Pros: alleviates the con in scenario 1
Cons: Slow - with my data this takes about 8 - 9 minutes.
Note: my datasource is a CSV file from our ERP system. The attached has sample data as a Table source.
Am I missing something obvious for this or is there a better approach to this?
Thanks,
Nick