Forum Discussion

Copper Contributor

Nov 13, 2019

Solved

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 '>' i...

excel

Formulas and Functions

power bi

SergeiBaklan

Nov 14, 2019

RaMa87

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.

PQ Number of Separators in Text.xlsx21 KB

RaMa87

Copper Contributor

Nov 14, 2019

SergeiBaklan

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

#"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.

SergeiBaklan

Diamond Contributor

Nov 14, 2019

RaMa87

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.

PQ Number of Separators in Text.xlsx21 KB

RaMa87
Copper Contributor
Nov 18, 2019
SergeiBaklan Thanks! Had to modify a bit, since I am importing from external text files but it did work out in the end:
#"Step name" = Table.AddColumn(#"Previous step", "New Column name", each List.Count(Text.Split([Column with information],">"))-1),
- SergeiBaklan
  Diamond Contributor
  Nov 18, 2019
  RaMa87 , you are welcome. Yes, above was just small sample to illustrate how the function works, not exact patter.