topic Re: Seeking for a way to split the column which are not in same format in DAX Commands and Tips

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

Anonymous — Wed, 20 Oct 2021 02:08:30 GMT

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.

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

VahidDM — Wed, 20 Oct 2021 02:25:09 GMT

Hi Anonymous

Can you share a result table you are looking for?

Appreciate your Kudos!!

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

Anonymous — Wed, 20 Oct 2021 02:32:58 GMT

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

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

VahidDM — Wed, 20 Oct 2021 03:21:41 GMT

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!!

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

Anonymous — Wed, 20 Oct 2021 04:47:07 GMT

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

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

VahidDM — Wed, 20 Oct 2021 05:10:22 GMT

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!!

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

Anonymous — Wed, 20 Oct 2021 06:00:09 GMT

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.

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

Anonymous — Wed, 27 Oct 2021 02:16:01 GMT

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.