Apply Text Between Delimiters Dynamically

Power Query
1

Hi all,

I am stumped on this one and hope someone in the forum can help. I have a column called Previous Campaign that have appended data separated by semicolon. In another column, I have the Previous Interaction Point going from 1- 10.

I would like to find the appropriate campaign based on the value in the interaction point.

For instance, if the value is 1, then I would like to find the first campaign. If the value is 5, i would like to find the fifth campaign that is appended.

How can I apply text between delimiters dynamically?

Can someone help? I will also do some more research and hope I explained myself well. Thanks in advance?

image
image907Ă—603 20.3 KB

image
image1117Ă—317 28 KB

Attribution Test.pbix (118.9 KB)

2

Hi Yuhanna,

did you change your post? Anyway, for the original task this should do.
Please note that quite some times you have higher numbers, than elements in the column.
So the relation might be more shaky then you think it is: You might get random elements.
But that is a data issue, not a Power Query problem.

:warning: I don’t have access to your one drive, so you might need to change column names!

let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Previous Campaign", each 
        try Text.Split([Campaign Name], "; "){Number.From(Text.BetweenDelimiters([Previous Campaign Interaction Point], "(", ")"))-1}
        otherwise "There weren't enough elements in Campaign Name.")
in
    #"Added Custom"

Regards,
Matthias

2 Likes
3

Hi Matthias.

Thanks for the post. I am working on fixing the data issue at the source level. I know there are instances
where the numbers don’t match.

I didn’t change the request. I thought the extract function would work. Sorry for the confusion.

Thanks for the quick response. Will try it and let you know the results.

4

The extract function works. :blush: Just applied differently than you thought.
I orientated myself on Campaign Previous Values 1-10 (2) as it had the most rows.
=> so the code will fit there, but for other queries you might need to adjust the column names.

5

Hi Matthias

I have filtered for Previous Value (4) for Record ID 47097051 as an example for you to see what I am seeing.

As you can see in the image # 1 below, there are multiple campaigns in the column Previous Campaign. But when I applied the syntax two things happen as you can see in image #2.

  1. It added multiple rows
  2. It shows the error message “There wasn’t enough elements, etc.” when there are. So it shouldn’t generate an error but the name of the 4th campaign in the text string.

I have provided access to a test excel file as well as the pbix file for your help.

Image #1

image
image1248Ă—891 36.3 KB

Image #2

image
image1062Ă—118 10.4 KB

Excel test file

Pbix test file

1 Like
6

Hi @ysherriff,
I think you have not implemented the code as @Matthias commented:

Your code:

#"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Custom", each Table.AddColumn(Source, "Previous Campaign", each
        try Text.Split([[Previous Campaign]], "; "){Number.From(Text.BetweenDelimiters([[Previous Interaction Point Value]], "(", ")"))-1}
        otherwise "There weren't enough elements in Campaign Name.")),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Previous Campaign"}, {"Custom.Previous Campaign"})

This is with the code from @Matthias:

#"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Custom", each
         try Text.Split([Previous Campaign], "; "){Number.From(Text.BetweenDelimiters([Previous Interaction Point Value], "(", ")"))-1}
        otherwise "There weren't enough elements in Campaign Name.")

You can also check if there are enough elements in the column like this:

#"Added Custom1" = Table.AddColumn(
    #"Filtered Rows1",
    "Custom1",
    each
      if List.Count(Text.ToList(Text.Select([Previous Campaign], ";"))) >=
        Number.From(Text.BetweenDelimiters([Previous Interaction Point Value], "(", ")")) - 1
      then
        Text.Split([Previous Campaign], "; ")
        {
          Number.From(Text.BetweenDelimiters([Previous Interaction Point Value], "(", ")")) - 1
        }
      else
        ""
  )

Regards

Attribution Test_JAFP.pbix (101.3 KB)

1 Like
7

Thank you both and yes I implemented it wrong. Thank you for the insight.

Love this forum and contributors.

Take care!!!

2 Likes
8

Thanks José Antonio for stepping in!

1 Like