Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
ID | Column |
1 | PowerBI-Dax100-Microsoft-2018 (3 delimiters) |
2 | PowerBI-Dax100-Microsoft-2018-2021 (4 delimiters) |
3 | Power Automate-END100-2016 (2 delimiters) |
4 | PowerBI-Automate-Dax100-End100- 2021-2021 (5 delimiter's) |
Solved! Go to Solution.
@Anonymous
You can use split and merge in power query, I duplicated the column and just remove the digits other the year by replace "100" with nothing, then you can just split by non-digits and digits.
Please copy the code in advanced editor or just download the example pbix to check the applied steps in query editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrIL08tcvLUdUmsMDQw0PXNTC7KL85PK9E1MjC0UIrViVYyIqQKSBgZgpUaw5QqOJaW5OcmlqTquvq5gHQA1ZmBlZggmQZXBDXWNS8FRCmAzIMaGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column", "Column - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","100","",Replacer.ReplaceText,{"Column - Copy"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Column - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column - Copy.1", "Column - Copy.2", "Column - Copy.3"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Column - Copy.2", "Column - Copy.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Year")
in
#"Merged Columns"
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Anonymous
You can use split and merge in power query, I duplicated the column and just remove the digits other the year by replace "100" with nothing, then you can just split by non-digits and digits.
Please copy the code in advanced editor or just download the example pbix to check the applied steps in query editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrIL08tcvLUdUmsMDQw0PXNTC7KL85PK9E1MjC0UIrViVYyIqQKSBgZgpUaw5QqOJaW5OcmlqTquvq5gHQA1ZmBlZggmQZXBDXWNS8FRCmAzIMaGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column", "Column - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","100","",Replacer.ReplaceText,{"Column - Copy"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Column - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column - Copy.1", "Column - Copy.2", "Column - Copy.3"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Column - Copy.2", "Column - Copy.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Year")
in
#"Merged Columns"
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Can you share a result table you are looking for?
Appreciate your Kudos!!
ID | Column | Result column |
1 | PowerBI-Dax100-Microsoft-2018 | 2018 |
2 | PowerBI-Dax100-Microsoft-2018-2021 | 2018-2021 |
3 | Power Automate-END100-2016 | 2016 |
4 | PowerBI-Automate-Dax100-End100- 2021-2021 | 2021-2021 |
Thanks for following up
Hi @Anonymous
Try this to add a new column:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi VahidDM,
I am getting an error while using that function.
The error was - "The Search Text provided to function SEARCH could not be found in the given text.
Thanks
Hi @Anonymous
That because there is value in the column that does not have year, so Try this:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
ID | Column | Result column |
1 | PowerBI-Dax100-Microsoft-2018 | 2018 |
2 | PowerBI-Dax201-Microsoft-2018-2021 | 201-Microsoft-2018-2021 |
3 | Power Automate-END100-2016 | 2016 |
4 | PowerBI-Automate-Dax205-End100- 2021-2021 | Dax205-End100- 2021-2021 |
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
87 | |
77 | |
52 | |
37 | |
21 |