Count Months/Periods That have Data Starting M0

[Anonymous]

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 Power BI extactly the same format...

So in row 20 we have data for 10 periods starting at M0 up until M9...

[v-eqin-msft · Accepted solution]

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.

[Anonymous]

Hi Eyelyn9... could kindly cast your eye over

https://community.powerbi.com/t5/Desktop/TRICKY-Custom-Column-in-Power-Query/m-p/1760455#M690967

Everyone on community forum is struggling 

[Anonymous]

Hi Eyelyn can you assist with https://community.powerbi.com/t5/Desktop/Move-Calculated-Column-Measure-to-Power-Query/m-p/1754124#M...

TRICKY

[Anonymous]

many thanks Eyelyn Qin, much appericated

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

Hi Amit...

How do you count at row level for each period from M0 onwards i.e. for row 20. that coun is 10 because we have values in period M0-M9...