Can PowerQuery Determine How Many Concatenations are in a field?

[gemcityzach]

Hey there, I have a data set where a few of the fields contain concatenated values. The values get that way because form fields will allow the user to select multiple inputs. The field is pipe delimited. How could I go about (1) determining if any given record contains concatenated values in those fields, and (2) count how many possible concatenations are in each given field for a given record?

ID	Title	Concat_1	Concat_2	concat_1_concat?	concat_2_concat?	Concat_1_count	Concat_2_count
1	a	abc|def|pqr	Item1|Item2|Item3|Item4	Yes	Yes	3	4
2	b	abc	Item1	No	No	1	1
3	c	abc|def|pqr|xyz	Item2	Yes	No	4	1

Edit: Figure out part 1. Using the following tells me if a PIPE/concatenated value is present

each if [field_name] = null then 0 else Text.Contains([field_name]), "|") then 1 else 0

[AMeyersen · Accepted solution]

Hi @gemcityzach ,

are you looking for something like this?

#"Added Conditional Column" = Table.AddColumn(
    #"previous step", 
    "Concat_1_count", 
    each Text.Length(Text.Select([Concat1], "|")) + 1, 
    type number
  ), 
  #"Added Conditional Column1" = Table.AddColumn(
    #"Added Conditional Column", 
    "Concat_1_concat?", 
    each if [Concat_1_count] > 1 then "TRUE" else "FALSE", 
    type logical

 

[gemcityzach]

That's more or less what I ended up doing. For the detect pipes I used a text.contain and then to count pipes I did exactly what you suggest and added +1 to account for the starting index value that is almost always there.

[djurecicK2]

What do you mean by "How many possible concatenations are in each given field for a given record?" What is the expected result from your sample data?

[gemcityzach]

The expected result is in the example. ID 1 shows the count of concatenated values in the different reference fields.

[gemcityzach]

Not sure I understand the List.Max bit. The code you provided appears to give me the count of multiple pipes in the field.