Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |