Split text but result align to the right

[FredLEGUEN]

Hi the group,

I have a list of addresses to split

ZA BP63072, 61 Rue de Vitré, 35130 La Guerche-de-Bretagne, France

Col du Barioz, 38570 Theys, France

11 Le Hil, 35230 Noyal-Châtillon-sur-Seiche, France

16 Avenue de la Gare, 80200 Doingt, France

53 Rue du 22ème B.M.N.A, 83260 La Crau, France

223 Avenue des Lacs, 40990 Saint-Paul-lès-Dax, France

6 Rue Gutenberg, 44160 Pontchâteau, France

63 Avenue Edouard Herriot, 82300 Caussade, France

55 B Rue Benoît Fourneyron Zone Artisanale de, Chemin de l'Étang, 26780 Châteauneuf-du-Rhône, France

Zone Industrielle 4 Nations, 1 Rue Edouard Belin, 44360 Vigneux-de-Bretagne, France

245 Rue Ferdinand Fert, 26110 Nyons, France

Rue de l'Oratoire, 74140 Douvaine

Chemin des Boucheroz, 74210 Faverges, France

The addresses don't have the same number of comma (the delimitor).

So when I split I have the following result

63 Avenue Edouard Herriot	82300 Caussade	France
55 B Rue Benoît Fourneyron Zone Artisanale de	Chemin de l'Étang	26780 Châteauneuf-du-Rhône	France
Zone Industrielle 4 Nations	1 Rue Edouard Belin	44360 Vigneux-de-Bretagne	France
245 Rue Ferdinand Fert	26110 Nyons	France
Rue de l'Oratoire	74140 Douvaine
Chemin des Boucheroz	74210 Faverges	France

Is there a way to return the result but with the folowing result?

	63 Avenue Edouard Herriot	82300 Caussade	France
55 B Rue Benoît Fourneyron Zone Artisanale de	Chemin de l'Étang	26780 Châteauneuf-du-Rhône	France
Zone Industrielle 4 Nations	1 Rue Edouard Belin	44360 Vigneux-de-Bretagne	France
	245 Rue Ferdinand Fert	26110 Nyons	France
		Rue de l'Oratoire	74140 Douvaine
	Chemin des Boucheroz	74210 Faverges	France

Thanks

@ImkeF , that's a challenge for you 😉

[ImkeF · Accepted solution]

Hi @FredLEGUEN ,

you can use this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVJLbtswEL3KwGvLoKifs7SUOimQukZSZBEnC9aaWgSYIUCRRtwb9BpdpZuuegNdrCPZaaIA3c1i3m/mbTaTuwWU6zwRhZxCHsN1QKgRbrV33a8pJFmcCLhScBHQbRuMaoxKh17tCKewdIq2OHmYbiaVNVAHKJXT9jvj5lkh4EuDh3a0FsdwhXCpTU8tmXplD8pEVdP99NoYS1EbXHSDmrXGwBwWe6SjO8N+lOOFuZBCwLnVtPOj9Sw5JgkgZff8iFDOPs1WswVDEpkPiSqnwggjZfKq0fLKlr2n4uxMwI3S5KO1CiYy3XMbnaunETQf1C6CR/qKbsewNGaVtSW/7aPhO6n8n9KH2gblarhEx5fjEHO+ioBKhbZV9fgGWQblIFQi2e63h6UNjvDgLMGdJYSF87pVpEyfYApVg4+ahoPdByFk0f3witidzIs5a5ycEYZvUR2i66b78+6rA+tHqkPrnUbDvCmslNeW+DTHsrwEKNFo6oMnHPxWcz/C03/bItNsAC/R1ZoU1f3ke2NxzJ04DPxv9k+tPMX47JS3uv9/kcZp//+w5wedivgSuoXSBq6R6/tYpJKJl2rP38E33A9/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromRows({List.Reverse(Text.Split([Column1], ","))})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column4", "Column3", "Column2", "Column1"})
    in
    #"Expanded Custom"

Enjoy 😉