How to split data in which has data is not uniform

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