Forum Discussion

peppelino's avatar
peppelino
Frequent Visitor
3 years ago

Count columns that are not blank, null or zero based on column string condition

Hi, 

 

I've got a table with a dynamic amount of columns that have metrics based on locations.

I want to be able to count the columns that are not blank, not null and not zero based on a condition where the column name ends with a specific string.

example table below:

 

productproduct_groupUSA minsCAN minsSWE minsUSA profitCAN profitSWE profitUSA (another metric etc)

bike

bikes1null(blank)2004002001
carcars110800010000100000

 

 

in the above example I want to count any column header that ends with the word mins:

bikes = 1 mins

car = 2 mins

 

a sort of combination of these:

 

 

 

 

List.Count(List.Difference(List.Range(Record.FieldValues(_),3), {null, "", 0}))

 

 

 

 

 

 

 

 

 

List.Sum(List.Transform(List.Select(Record.FieldNames(_), each Text.EndsWith(_, "mins")), (name) => Record.Field(_, name)))

 

 

 

 

 

 

any help is appreciated.

Need Help

2 Replies

  • JW_van_Holst's avatar
    JW_van_Holst
    Resolver IV
    3 years ago

    This would be my approach: 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsrMTlXSAVPFQNoQiCHIyMAASJqASQjbUClWJ1opObEIpBBKGUIxSN7CAKLMAJU2UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product = _t, product_group = _t, #"USA mins" = _t, #"CAN mins" = _t, #"SWE mins" = _t, #"USA profit" = _t, #"CAN profits" = _t, #"SWE profits" = _t, #"USA (other metrics)" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,null,"0",Replacer.ReplaceValue,{"product", "product_group", "USA mins", "CAN mins", "SWE mins", "USA profit", "CAN profits", "SWE profits", "USA (other metrics)"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","0",Replacer.ReplaceValue,{"product", "product_group", "USA mins", "CAN mins", "SWE mins", "USA profit", "CAN profits", "SWE profits", "USA (other metrics)"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"product", "product_group"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Mins", each Text.Contains([Attribute], "mins")),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Mins] = true) and ([Value] = "1")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"product"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"product"}, #"Grouped Rows", {"product"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
    #"Expanded Grouped Rows"

    a

     

    • peppelino's avatar
      peppelino
      Frequent Visitor
      3 years ago

      Hi JW_van_Holst , 

       

      I doubt this will work, I will not be able to specify the column names in any formula as I don't know what Country will be included in the dataset at any point. The Example with Canada, USA, Sweden and Denmark might be in the dataset today, but I will have 50 different ones tomorrow.

       

      Thanks

      Peter