Power Query - count of all rows where cell starts with text of current row's cell

1
votes

I am trying create the equivalent of an Excel formula in Excel's Power Query editor. I have an Excel file with a bunch of rows, and a column containing the URL path of the file/folder in a normal Windows structure. I am trying to use this to count up all rows/files/folders which are "this folder", or deeper files/subfolders.

the current Excel formula is: 

=COUNTIF(A$2:A$238602,A2&"*")

The idea is to get the path-value of the current row (A2), and check all the other rows (A$2:A$238602) to see if the those rows start with (A2&"*") the same text, and then count up all those rows where it does (COUNTIF).The data looks a bit like this:

X:\Admin\
X:\Admin\Records\
X:\Admin\Records\Delegated 2019\
X:\Admin\Records\Delegated 2019\Management.xlsx
X:\Admin\Records\Delegated 2019\Business.xls
X:\Admin\Records\Monitoring_2019.xlsx
X:\Admin\Records\Progression 2018.docx
X:\Admin\Leader Meetings\
X:\Admin\Leader Meetings\Leader Meeting 10 May 2016.docx
X:\Admin\Leader Meetings\Cross Team Group.docx
X:\Admin\Templates\
X:\Admin\Templates\Business Card Template.pdf
X:\Admin\Templates\Excel\
X:\Admin\Templates\Excel\PivotTable for SubProjects.docx
X:\Apprentice\
X:\Apprentice\Meetings\

so, the result for "X:\Admin\" is 14, "X:\Admin\Records\" is 6, "X:\Admin\Leader Meetings\" is 3, etc.

Thanks

1
excelpowerquery

1 Answers

1
votes

You can count the number of elements in the path column that start with the current path like this:

= Table.AddColumn(Source, "Count",
    (row) => List.Count(List.Select(Source[Path], each Text.StartsWith(_, row[Path]))),
  Int64.Type)

This takes the entire column (list) Source[Path] and selects only the elements that satisfy the condition that it starts with the current row's path value and then counts how many elements are in that filtered list.

Result