Hi Bax,

You can use Add Column > Extract > Text before delimiter for the location and text after delimiter for the shift. The lookup tables are then redundant.

Mynda

Hi Mynda,

That would work for the Region as in my example file the region is always at the start of the text string. However in the full live data set it is not always in that position. Also I wanted to pull out and match the Shift part if the description and the same applies to this. For example there are two entries in the data set as follows:

"Central & West - Controller of Site Safety - Midweek Day"

"Central & West - Controller of Site Safety - Call Off - Midweek Day"

I think you can use the extract option in conjunction with the advanced options to do it as they occur at the end of the text but in the live data this could appear anywhere say as follows:

"Central & West - Controller of Site Safety - Midweek Day - Call Off - 2021/22"

or

"Controller of Site Safety - Midweek Day - Central & West- Call Off - 2021/22"

You get the idea. It is random. Any idea how I would do this?

Thanks

Bax

Hi Mynda,

I think I have worked out how to do what I need. I have used Table.SelectRows and Text.Contains functions.

I added a custom column with the following formula to get the Region:

let CurrentText = [PO Line Description] in Table.SelectRows(Table_Region,each Text.Contains(CurrentText, [Region]))

This created a column with table values. I then expanded the column and it shows me the Region for those lines where there is a match in the text.

I did the same for the Shift field.

I have attached my file showing the solution.

Thanks

Bax

Glad you figured out a solution.

Your example data did not illustrate that the region could sometimes be in the middle, nor did you explain that initially. You did mention that the position of the shift could be 2 or 3 delimiters in, which could be handled with 'text after delimiter'. For this reason, it's best if your sample file can illustrate all scenarios, that way we fully understand the issues to be handled.

Mynda

Hi Mynda,

Consider my wrists well and truly slapped 🙂 I did try and make my initial enquiry as clear as I could. I thought that the second example of the shift that I needed would cover the issue where the bit I wanted could be anywhere in the text string.

Anyway I thought I would just add a bit of a warning to my solution having looked in to it a bit further. You need to be careful of duplicates. For example if you look in the solution file I uploaded and filter on PO Number 1332757 you will see that there are two lines for this in the Query sheet. One shows the Shift as Weekend, the other Weekend/B.Hol

The reason for this is because I have used the Text.Contains function to look for values in the Shift table in the Lookups sheet. That list contains "Weekend" so the lookup has picked up that this is in the PO Line Description field. The list also contains "Weekend/B.Hol" so it has then matched this and returned the second match.

I'm not sure if there is a clever way around this but useful to know that you might get duplicates which you will need to deal with.

Thanks

Bax