• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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.
 

Attachments

  • Sample File.xlsx
    8.8 KB · Views: 8
A Power Query Solution

Code:
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"
 
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

Code:
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"
 
Back
Top