Forum Discussion
Can PowerQuery Determine How Many Concatenations are in a field?
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
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
6 Replies
Not sure I understand the List.Max bit. The code you provided appears to give me the count of multiple pipes in the field.
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?
The expected result is in the example. ID 1 shows the count of concatenated values in the different reference fields.
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 logicalThat'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.
