Forum Discussion
Extracted Text Before Delimiter using another column as the delimiter
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
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],"")
4 Replies
Can you please post some example data and the expected result to help illustrate your issue?
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 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],"")
