Forum Discussion
peppelino
3 years agoFrequent Visitor
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...
JW_van_Holst
3 years agoResolver IV
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
3 years agoFrequent Visitor
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