Power Query - how to get count of comma delimited values from a column

[LP280388]

Hi Team, 

I have a table with a column which has comma seperated values as below. 

I'm trying to add a column to show count of these delimited values.  for example: below has 49 delimited values. the column should show 49 next to this column.  

AL, AZ, AR, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, VT, VA, WA, WV, WI, WY

[Ashish_Mathur]

Hi,

This calculated column formula works

=LEN(Table1[Text])-LEN(SUBSTITUTE(Table1[Text],",",""))+1

Hope this helps.

[DataInsights · Accepted solution]

@LP280388,

In Power Query, try this in a custom column:

List.Count(Text.Split([State],","))

[LP280388]

@DataInsights Thank you very much for such a easy step.