Seeking for a way to split the column which are not in same format

[Anonymous]

Hello everyone,

Morning!

I hope you guys are doing well.

Appreciate all your replies

I had a column in Power BI, where the data was not in the same format, and I am looking for splitting that column where it starts with year.

For example 

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)

I want to split that column where it starts with years.

Thank you everyone.

Looking forward for your responses.

[Anonymous · Accepted 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.

[VahidDM]

Hi @Anonymous 

Can you share a result table you are looking for?

Appreciate your Kudos!!

[Anonymous]

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 

[VahidDM]

Hi @Anonymous 

Try this to add a new column:

Year Column = RIGHT([Column],LEN([Column])-SEARCH("20",[Column])+1)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

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 

[VahidDM]

Hi @Anonymous 

That because there is value in the column that does not have year, so Try this:

Year Column =

IFERROR(RIGHT([Column],LEN([Column])-SEARCH("20",[Column])+1),blank())

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

Thanks VahidDM,

It worked as I required. But It came up with a new problem where it was extracting wrongly for few columns.

For example

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

And here I want to say that for every value in that column has years at ending. 

I am looking to get only years out of that column.

Thank you.