Forum Discussion
Count Months/Periods That have Data Starting M0
Hi Experts I would like to count the number of Period (show as M0,M1,M2 etc...) in table blow. Similiar to how one would do a count in excel...for each row in the table. I have matrix table in Po...
Hi Anonymous ,
Please use the following formula in Power Query-->Add Custom Column:
=List.NonNullCount(List.Range(Record.FieldValues(_),2,11))The whole M syntax is:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc7JCQAxCAXQXv45MK7ViP23MTExhzAMiAs81AgwBoifGUJCc7giR0BayK/QFnoL3cWXsTZ2jH3WeBM/pH6r61xJVsfIfAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, Date = _t, M0 = _t, M1 = _t, M2 = _t, M3 = _t, M4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"Date", type date}, {"M0", Int64.Type}, {"M1", Int64.Type}, {"M2", Int64.Type}, {"M3", Int64.Type}, {"M4", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Period Length", each List.NonNullCount(List.Range(Record.FieldValues(_),2,11))) in #"Added Custom"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Please use the following formula in Power Query-->Add Custom Column:
=List.NonNullCount(List.Range(Record.FieldValues(_),2,11))
The whole M syntax is:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc7JCQAxCAXQXv45MK7ViP23MTExhzAMiAs81AgwBoifGUJCc7giR0BayK/QFnoL3cWXsTZ2jH3WeBM/pH6r61xJVsfIfAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, Date = _t, M0 = _t, M1 = _t, M2 = _t, M3 = _t, M4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"Date", type date}, {"M0", Int64.Type}, {"M1", Int64.Type}, {"M2", Int64.Type}, {"M3", Int64.Type}, {"M4", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Period Length", each List.NonNullCount(List.Range(Record.FieldValues(_),2,11)))
in
#"Added Custom"
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
many thanks Eyelyn Qin, much appericated