Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FredLEGUEN
Helper III
Helper III

Split text but result align to the right

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 😉

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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 😉

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Super User
Super User

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 😉

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors