How to split data in which has data is not uniform

[ShreyasK]

I have table like below which has building name or building number in single line. I want to split building name and building number in 2 separate columns but there is no unique delimeter to split the same. 

Actual data:

Building Name

Pune (H41)

Chennai (N51)

J24

Bangalore F74

Output Result expected after splitting Building Name and Number:

Building Name	Building Number
Pune	H41
Chennnai	N51
J24	J24
Bangalore	F74

Please suggest, how can I achieve this output..?

[v-yueyunzh-msft]

Hi , @ShreyasK 

According to your descrioption, you want to split data in which has data is not uniform.

For your data , It seems that there is a space between each Building Name and Building Number, we can divide it first and then get the corresponding value.

You can create a blank query in Power Query Editor:

And then you can put this M code in the "Advanced Editor" to test :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijNS1XQ8DAx1FSK1YlWcs5IzctLzFTQ8DOFingZmYBpp8S89MSc/KJUBTdzoEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Building Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Building Name", type text}}),
    Custom1 = Table.SplitColumn(#"Changed Type", "Building Name",(x)=>  Text.Split(x," "),2),
    #"Renamed Columns" = Table.RenameColumns(Custom1,{{"Building Name.1", "Building Name"}, {"Building Name.2", "Building Number"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Building Number]=null then [Building Name] else [Building Number]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Building Number"}),
    #"Renamed Columns1" = Table.TransformColumns(Table.RenameColumns(#"Removed Columns",{{"Custom", "Building Number"}}),{"Building Number",(x)=> Text.Remove(x,{"(",")"})       })
in
    #"Renamed Columns1"

You can update the Source in your side to test if have some special data ?

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[ShreyasK · Accepted solution]

@v-yueyunzh-msft Thank for your response. Somehow I was able to fix this issue in the source file itself.

But Surely, this will help someone in future.