Forum Discussion

NewbieJono's avatar
NewbieJono
Post Partisan
5 years ago

Split up addresses

i have a list of address in excel in this format

 

Address line 1

Address line 2

Town

Sa91pd

Address line 1

Address line 2

Town

Sa91pd

Address line 1

Address line 2

Town

postcode

 

the list is all in one coloumn and has multiple address. is there a method to have each address on one line with mutiple coloumns?

 

 

Need Help

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    NewbieJono , simple enough as every complete address consists of 4 rows,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJKUotLlbIycxLVTBUitVBEzICC4Xkl+eBGcGJloYFKZjKaKmzIL+4JDk/JVUpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),
    Custom1 = Table.FromRows(List.Split(Source[Address],4))
in
    Custom1

    • NewbieJono's avatar
      NewbieJono
      Post Partisan
      5 years ago

      sorry, to make this more complaicated some address have 5 rows!

       

      the postcode seem to be consitantly in captials if this is any help

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi NewbieJono ，

         

        Could you pls attach your expected output?It's a bit confused about your requirement.

         

         

         

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!