Extracted Text Before Delimiter using another column as the delimiter

[Brynut]

Hi, Hopefully someone can help as I am a bit stumped...

I have two columns, one column contains the same data as the other but is concatenated with other data, I am trying to use 'Extracted Text Before Delimiter' to extract this but using a delimiter of the data from another column.

This is what I have so far....

let
Source = Salesforce.Data(),
KimbleOne__ResourcedActivity__c = Source{[Name="KimbleOne__ResourcedActivity__c"]}[Data],
#"Sorted Rows" = Table.Sort(KimbleOne__ResourcedActivity__c,{{"CreatedDate", Order.Descending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Id", "Name", "KimbleOne__FullName__c"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Other Columns", {{"KimbleOne__FullName__c", each Text.BeforeDelimiter(_, {"Name"}), type text}})
in
#"Extracted Text Before Delimiter"

but I get a error in the 'KimbleOne__FullName__c' column for every row of "Expression.Error: We cannot convert a value of type List to type Text. Details: Value=List Type=Type".

Any ideas? I am new to Power Bi so apologies in advance if I have to ask any clarifying questions.

Thanks Simon

[d_gosbell]

Can you please post some example data and the expected result to help illustrate your issue?

[Brynut]

Hi, of course please see example below...

• Column1  - CustomerNameResourceName  -  Data example...  MicrosoftJohn Smith
• Column2  - ResourceName  -  Data example...  John Smith

With the above I need to extract the CustomerName by using column 2 as the delimiter, the customer name and resource name vary in length from row to row.

Thanks

Simon

[Brynut]

Thank you for you quick response and answer which worked perfectly.

[d_gosbell · Accepted solution]

---

@Brynut wrote:

Hi, of course please see example below...

• Column1  - CustomerNameResourceName  -  Data example...  MicrosoftJohn Smith
• Column2  - ResourceName  -  Data example...  John Smith

With the above I need to extract the CustomerName by using column 2 as the delimiter, the customer name and resource name vary in length from row to row.

---

To do that I would just add a custom column with the following expression:

Text.Replace([CustomerNameResourceName],[ResourceName],"")