Forum Discussion
Finding a way to split the column
You just need to add a custom column to your existing query and put this formula in the pop-up box. Replace Exams with your actual column name.
Text.Combine(List.Select(List.LastN(Text.Split([Exams], "-"),2), each Text.Contains(_, "2")), "-")Pat
The result should look like
| Numbers | Exams | Years |
| 1 | DAX-100-PowerBI-2018-2021 | 2018-2021 |
| 2 | 20-DAX-200-PowerBI-2021 | 2021 |
| 3 | 201-DAX-205-powerBI-2019-2020 | 2019-2020 |
Thanks
Microsoft EmployeeHere's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJxjNA1NDDQDcgvTy1y8tQ1MjC0ABJGIDkEO1YnWskILKIL0mCEogGqFqrMGKIRqs5UtwBhsCVIsQFEHsqOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numbers = _t, Exams = _t, Years = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"Exams", type text}, {"Years", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select(List.LastN(Text.Split([Exams], "-"),2), each Text.Contains(_, "2")), "-"), type text)
in
#"Added Custom"
Pat
Hi Pat,
Morning!
Thanks pat, it seems to be working - But, how can I change that code to my scenario
could you please ellobrate it. (While I am trying to perform in the same way.
Added custom column,
(Paste the code you provided)
from "Table.AddColumn" then it was creating a new table and while expanding I selected only the new column I required.
But, few columns were working fine others are wrongly misplaced.
Thanks pat,
looking forward to hear from you.
- mahoneypat
You just need to add a custom column to your existing query and put this formula in the pop-up box. Replace Exams with your actual column name.
Text.Combine(List.Select(List.LastN(Text.Split([Exams], "-"),2), each Text.Contains(_, "2")), "-")Pat