Help : Subtotal within the same row for specific row value(s)

[Murari Rajagopalan]

Folks,
I have attached the sample file where in the location code and the products under the location is mentioned one below the other. Location code sample : 203010
I would need the count of products at location code as per the output required format mentioned in the same file. the location code will always be a numeric with 6 digits for delimeters logic if any. Can someone help me with the formula to get the relevant output ? thanks in advance.

 

[AlanSidman]

A Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.From([Data Input]) otherwise "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Text" then null else [Custom]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Data Input] <> 203010 and [Data Input] <> 213211 and [Data Input] <> 303012 and [Data Input] <> 403013)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom.1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Murari Rajagopalan]

thank you very much. As i said, its the sample. I have 6000 such location codes or Data Input as per the code. Do i need to pass only in the query all 6000 or can this managed by refering the values in another array and then do the count with the actual data ?
Appreciate the help here.

A Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.From([Data Input]) otherwise "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Text" then null else [Custom]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Data Input] <> 203010 and [Data Input] <> 213211 and [Data Input] <> 303012 and [Data Input] <> 403013)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom.1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Murari Rajagopalan]

thanks, again. I will give it a try. Appreciate the quick help