Running Total Distinct Count

[Craigc3814]

I have a table I am working with that has project names that are listed multiple times (along with a bunch of other data that should not be relevant here). This is in Power Query

What I want to do is a running total of how many times the project name appears then start the count over when a new project name appears. End result would look like below.

Project A 1
Project A 2
Project A 3
Project B 1
Project B 2
Project C 1

 

[theBardd]

When it groups, the multiple instances are reduced to 1 each, but the grouped tables are then expanded which should get you back to where you were.

Can you post the data in a table here?

 

[sandy666]

create blank Query and replace code with:

[SIZE=1](MyTable as table) as table =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "RunningTotCountOver", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Source[Count],{0},(Cumulative,Count) => Cumulative & {List.Last(Cumulative) + Count})),
    AddedRunningTotCountOver = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningTotCountOver[/SIZE]

rename Query1 to fnRunningTotCountOver

replace code for your table with:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="[COLOR="#FF0000"]Table3[/COLOR]"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Group = Table.Group(Index, {"Projects", "Index"}, {{"Count", each Table.RowCount(_), type number}}),
    RC = Table.RemoveColumns(Group,{"Index"}),
    TableType = Value.Type(Table.AddColumn(RC, "RunningTotCountOver", each null, type number)),
    Group2 = Table.Group(RC, {"Projects"}, {{"AllData", fnRunningTotCountOver, TableType}}),
    Expand = Table.ExpandTableColumn(Group2, "AllData", {"RunningTotCountOver"}, {"RunningTotCountOver"})
in
    Expand[/SIZE]

change table name suitably

I did it with longer names and with Index but theBardd way is shorter

edit:
as I said from the begining: adapt not copy/paste

 

[Craigc3814]

When it groups, the multiple instances are reduced to 1 each, but the grouped tables are then expanded which should get you back to where you were.

Can you post the data in a table here?

Just ran out to lunch then have a meeting. I cannot post the actual data as it is government projects. I can post the code as soon as I get back this afternoon. Adding mockup data would be easy as there are not many columns.

Thanks in advance for all of your help

 

[theBardd]

...but theBardd way is shorter

theBardd's small adaptation of Marcel's way

 

[sandy666]

theBardd's small adaptation of Marcel's way

exactly the same (longer)

 

[Craigc3814]

Here is my current code (minus what you added). Clearly I am doing more than what I shared with you and I am sure that is where the issue lies.

I basically start with an input table for people that have almost 0 Excel experience to update. It only has these columns

Budget ID

Project Name

Renamed

Division/Department

Project Manager

Spending Geometry

Status

Est. Start Date

Est. End Date

<tbody>
</tbody>

Funny you mentioned Marcel because some of his code is in here. What I am trying to do is format this data so that I can get it into a pivot table and show spend by month for the next 5 or so years (I can handle that, getting the format is my problem).

I used a portion of Marcel's code to say if a project is 3 months long create one line for each month (really just months remaining).

So the project name (project A as we have been referencing has 3 months remaining (April, May, & June).

I created the 3 lines in hopes that I could then get a count of each line and add months based on the count (Hopefully that makes sense). So for Project A line 2 I was going to add 2 months to our current month and make that the budget line for May, for line 3 I was going to add 3 months and make that our budget line for June.

As mentioned I got your code to run but it groups everything to 1 line and does not ungroup it back. 100% positive it is user error.


let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Budget ID", type any}, {"Project Name", type text}, {"Renamed", type text}, {"Division/Department", type text}, {"Project Manager", type text}, {"Spending Geometry", type text}, {"Status", type text}, {"Est. Start Date", type date}, {"Est. End Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Spending Geometry] = "Linear") and ([Status] = "Deferred" or [Status] = "Future" or [Status] = "In Progress")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Est. Start Date", Order.Ascending}}),
#"Months Between" = Table.AddColumn(#"Sorted Rows", "Duration", each ([Est. End Date]-[Est. Start Date])/30.5),
#"Changed Type1" = Table.TransformColumnTypes(#"Months Between",{{"Duration", Int64.Type}}),
#"Todays Date" = Table.AddColumn(#"Changed Type1", "Today's Date", each DateTime.LocalNow()),
#"Changed Type2" = Table.TransformColumnTypes(#"Todays Date",{{"Today's Date", type date}}),
#"Months Remaining" = Table.AddColumn(#"Changed Type2", "Months Remaining", each ([Est. End Date]-[#"Today's Date"])/30.5),
#"Changed Type3" = Table.TransformColumnTypes(#"Months Remaining",{{"Months Remaining", Int64.Type}}),
#"Percentage Complete" = Table.AddColumn(#"Changed Type3", "Percentage Complete", each 1-([Months Remaining]/[Duration])),
#"Added Custom" = Table.AddColumn(#"Percentage Complete", "Percent Complete", each if[Percentage Complete]<0 then 0 else [Percentage Complete]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Percent Complete", Percentage.Type}}),
#"Rounded Off" = Table.TransformColumns(#"Changed Type4",{{"Percent Complete", each Number.Round(_, 2), Percentage.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Rounded Off",{"Percentage Complete"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each [Months Remaining] > 0),
#"Repeated" = Table.TransformColumns(#"Filtered Rows1", {"Months Remaining", each List.Numbers(_,_,0), type list}),
#"Expanded" = Table.ExpandListColumn(Repeated, "Months Remaining")
in
Expanded

 

[theBardd]

You don't make it easy showing the headings but no data, but this seems to work fine as far as I can see

let
    Source = Excel.CurrentWorkbook(){[Name="tblProjects"]}[Content],
    allColumns.Type = Table.TransformColumnTypes(Source,{{"Budget ID", type any}, {"Project Name", type text}, {"Renamed", type text}, {"Division/Department", type text}, {"Project Manager", type text}, {"Spending Geometry", type text}, {"Status", type text}, {"Est. Start Date", type date}, {"Est. End Date", type date}}),
    activeProjects.Filter = Table.SelectRows(allColumns.Type, each ([Spending Geometry] = "Linear") and ([Status] = "Deferred" or [Status] = "Future" or [Status] = "In Progress")),
    startDate.Sort = Table.Sort(activeProjects.Filter,{{"Est. Start Date", Order.Ascending}}),
    monthsDuration.Add = Table.AddColumn(startDate.Sort, "Duration", each ([Est. End Date]-[Est. Start Date])/30.5),
    duration.Type = Table.TransformColumnTypes(monthsDuration.Add,{{"Duration", Int64.Type}}),
    today.Add = Table.AddColumn(duration.Type, "Today's Date", each DateTime.LocalNow()),
    today.Type = Table.TransformColumnTypes(today.Add,{{"Today's Date", type date}}),
    monthsLeft.Add = Table.AddColumn(today.Type, "Months Remaining", each ([Est. End Date]-[#"Today's Date"])/30.5),
    monthsLeft.Type = Table.TransformColumnTypes(monthsLeft.Add,{{"Months Remaining", Int64.Type}}),
    pctCPt.Add = Table.AddColumn(monthsLeft.Type, "Percentage Complete", each if 1-([Months Remaining]/[Duration]) < 0 then 0 else 1-([Months Remaining]/[Duration])),
    pctCpt.Type = Table.TransformColumnTypes(pctCPt.Add,{{"Percentage Complete", Percentage.Type}}),
    pctCpt.Roundoff = Table.TransformColumns(pctCpt.Type,{{"Percentage Complete", each Number.Round(_, 2), Percentage.Type}}),
    #"Filtered Rows1" = Table.SelectRows(pctCpt.Roundoff, each [Months Remaining] > 0),
    monthsLeft.List = Table.TransformColumns(#"Filtered Rows1", {"Months Remaining", each List.Numbers(_,_,0), type list}),
    monthsLeft.ListExpand = Table.ExpandListColumn(monthsLeft.List, "Months Remaining"),
/* my bit added here ------------------------------- */
    countColumnAdd = Table.AddColumn(monthsLeft.ListExpand, "Instance", each 1),
    TableType = Value.Type(Table.AddColumn(countColumnAdd, "Running Count", each null, type number)),
    fnGrouping = (MyTable as table) as table =>
    let
        Source = Table.Buffer(MyTable),
        TableType = Value.Type(Table.AddColumn(Source, "Running Count", each null, type number)),
        Cumulative = List.Skip(List.Accumulate(Source[Instance],{0},(cumulative,instance) => cumulative & {List.Last(cumulative) + instance})),
        RunningCount = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
    in
    RunningCount,
    rowsGrouped = Table.Group(countColumnAdd, {"Project Name"}, {{"AllData", fnGrouping, TableType}}),
    groupsExpanded = Table.ExpandTableColumn(rowsGrouped, "AllData", {"Budget ID", "Project Nam", "Renamed", "Division/Department", "Project Manager", "Spending Geometry", "Status", "Est. Start Date", "Est. End Date", "Running Count"},{"Budget ID", "Project Nam", "Renamed", "Division/Department", "Project Manager", "Spending Geometry", "Status", "Est. Start Date", "Est. End Date", "Running Count"}
),
    projectOrder.Sort = Table.Sort(groupsExpanded,{{"Project Name", Order.Ascending}})
in
    projectOrder.Sort

 

[Craigc3814]

I apologize, if I put the project names in and somehow a contractor got a hold of the data and was able to review upcoming projects prior to everyone else learning of them I would get DESTROYED. This seems to work nearly perfect for whatever reason it adds everything back in that I filter out prior to your section of the code. If I move your section up in the code then do the filters I assume it fixes that issue.

I cannot thank you enough!! I consider myself very advanced in UI functions and simple formulas in M. I become awful once it gets to custom coding but things like this help me learn so much.

 

[theBardd]

I apologize, if I put the project names in and somehow a contractor got a hold of the data and was able to review upcoming projects prior to everyone else learning of them I would get DESTROYED.

You could provide dummy data, enough to show a typical situation.

This seems to work nearly perfect for whatever reason it adds everything back in that I filter out prior to your section of the code. If I move your section up in the code then do the filters I assume it fixes that issue.

Doesn't sound like a good idea to me, the running count will be done on unfiltered data, so you could lose some of the sequence when you then filter them. Also, I cannot see what you mean, the data looks fine to me in the example I created.