Forum Discussion
Split based on specifically formatted string
I have a column with a range of different strings in each row, but every one will include either L1, L2 or L3. So I want to create a custom column that just has that L1, L2 or L3 value. The standard split function doesn't quite seem to do the job. I guess this is basic stuff for the folks here :)
Yes, Forgot to mention it's Power Query (my signature indicates that most of my solutions are Power Query).
The entire code from the advanced query editor (I used Power Query in Excel, but with a different source it also works in Power BI):
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], PreviousStep = Table.TransformColumnTypes(Source,{{"String", type text}}), // Simple solution but not very flexible Simple = Table.AddColumn(PreviousStep, "IfThenElse", each if Text.Contains([String],"L1") then "L1" else if Text.Contains([String],"L2") then "L2" else "L3"), // More dynamic solution: returns the first element of the list that is found StringsToBeFound={"L1","L2","L3"}, Dynamic = Table.AddColumn(PreviousStep, "Lookup", (ThisRecord) => List.Select(StringsToBeFound, each Text.PositionOf(ThisRecord[String],_) >= 0){0}) in Dynamic
3 Replies
Sounds like extraction rather than splitting.
Anyhow I have 2 solutions in the code part below:
// Simple solution but not very flexible Simple = Table.AddColumn(PreviousStep, "IfThenElse", each if Text.Contains([String],"L1") then "L1" else if Text.Contains([String],"L2") then "L2" else "L3"), // More dynamic solution: returns the first element of the list that is found StringsToBeFound={"L1","L2","L3"}, Dynamic = Table.AddColumn(PreviousStep, "Lookup", (ThisRecord) => List.Select(StringsToBeFound, each Text.PositionOf(ThisRecord[String],_) >= 0){0})Nice. So that would be under a new column in the query editor? Reason for asking is I'm getting 'Token Eof expected'.
Yes, Forgot to mention it's Power Query (my signature indicates that most of my solutions are Power Query).
The entire code from the advanced query editor (I used Power Query in Excel, but with a different source it also works in Power BI):
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], PreviousStep = Table.TransformColumnTypes(Source,{{"String", type text}}), // Simple solution but not very flexible Simple = Table.AddColumn(PreviousStep, "IfThenElse", each if Text.Contains([String],"L1") then "L1" else if Text.Contains([String],"L2") then "L2" else "L3"), // More dynamic solution: returns the first element of the list that is found StringsToBeFound={"L1","L2","L3"}, Dynamic = Table.AddColumn(PreviousStep, "Lookup", (ThisRecord) => List.Select(StringsToBeFound, each Text.PositionOf(ThisRecord[String],_) >= 0){0}) in Dynamic
