Forum Discussion
Power Query: Count specific values in a column
Hi all,
I have a list in a Power Query with a column which contains text like this:
A1->1 C1->2 B1->3 D1->4 E1->5 G1->6 F1->7 H1->8 A2->9 B2->11
I would need a command which counts all '>' in this column and either creates a new column with that count or transforms the existing column to contain that counted value.
Can someone help me here?
Thanks and best wishes
You may add custom column with this formula
Generated script will be
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn( Source, "NumberOfSeparators", each List.Count(Text.Split([A],">"))-1) in #"Added Custom"Please check the sample in attached file.
5 Replies
Thanks for the answer!
I tried implementing that but I could not get I to work.
In which context (line of code) would I have to use that?
#"Previous Step" = List.Count(Text.Split([ColumnName],">"))-1
or
#"Previous Step" = Table.AddColumn(#"Respective column", "Desired Output", each if List.Count(Text.Split([ColumnName],">"))-1 else null),
Or am I doing something horribly wrong? Sorry - I am not what you would call an expert here.
You may add custom column with this formula
Generated script will be
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn( Source, "NumberOfSeparators", each List.Count(Text.Split([A],">"))-1) in #"Added Custom"Please check the sample in attached file.
