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
PhilippeMuniesa
Resolver I
Resolver I

Numeric and alpha split Column

Hello,I seek to divide the contents of an alphanumeric column into two ciolonnes, exclusively digital moon, the other containing the digital part of the column.

 

Intial situation

CompteNumCompteLib
  411FIDUCCLIENT 1
  411WEBERCLIENT 2
 411MILHEMCLIENT 3
4010FOURN1Fournisseur 1
4010FOURN1Fournisseur 1
4010FOUNR2Fournisseur 2
1010000000CAPITAL                       
1013000000CAPITAL SOUSCRIT APPELE VERSE 
1190000000REPORT A NOUVEAU SOLDE DEBIT  
1200000000RESULTAT DE L'EXERCICE - BENEF 

 

Expected outcome

 

CompteNumCompteLibCompteAuxNumCompteNum2
  411FIDUCCLIENT 1FIDUC411
  411WEBERCLIENT 2WEBER411
 411MILHEMCLIENT 3MILHEM411
4010FOURN1Fournisseur 1FOURNI14010
4010FOURN1Fournisseur 1FOURNI14010
4010FOUNR2Fournisseur 2FOURNI24010
1010000000CAPITAL                        1010000000
1013000000CAPITAL SOUSCRIT APPELE VERSE  1013000000
1190000000REPORT A NOUVEAU SOLDE DEBIT   1190000000
1200000000RESULTAT DE L'EXERCICE - BENEF  1200000000

 

Thank you for the help you can give me

 

Philippe Muniesa

 

 

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

On the Home tab of the query editor, under Split Column choose Digit to Non-digit.

 

mahoneypat_0-1608662882961.png

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Thank you, 

 

thank you, I did not know this feature, very convenient. In the meantime, I had finished not arrive at the same result with different lines of code, which I allow myself to deliver here for information Although the built-in function is much simpler.

 

CompteNum is the cowhich contains the numerical alpha data to split

 

let

Source = fx_Bal_Tiers(Ouvre_FEC1),
#"Colonnes permutées" = Table.ReorderColumns(Source,{"CompAuxNum", "CompAuxLib", "CompteLib", "Debit", "Credit", "Solde", "CompteNum"}),
Remplace_esp_rien_Dans_NUM = Table.ReplaceValue(#"Colonnes permutées"," ","",Replacer.ReplaceText,{"CompteNum"}),
#"Type modifié" = Table.TransformColumnTypes(Remplace_esp_rien_Dans_NUM,{{"CompteNum", type text}}),
#"Added Custom" = Table.AddColumn(#"Type modifié", "Custom", each Text.PositionOfAny([CompteNum],{"A".."Z"})),
#"Type modifié1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
Nombre_Car_Chaine = Table.AddColumn(#"Type modifié1", "Longeur_CAR", each Text.Length([CompteNum])),
#"Inserted First Characters" = Table.AddColumn(Nombre_Car_Chaine, "First Characters", each Text.Start([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Type modifié2" = Table.TransformColumnTypes(#"Inserted First Characters",{{"First Characters", Int64.Type}}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié2", "test", each if [Custom] > 0 then [Custom] else [Longeur_CAR]),
#"Inserted Text Range" = Table.AddColumn(#"Colonne conditionnelle ajoutée", "Text Range", each Text.Middle([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"CompteNum", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Range", "Number"}})
in
#"Renamed Columns"

 

 

Philippe Muniesa

View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

On the Home tab of the query editor, under Split Column choose Digit to Non-digit.

 

mahoneypat_0-1608662882961.png

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you, 

 

thank you, I did not know this feature, very convenient. In the meantime, I had finished not arrive at the same result with different lines of code, which I allow myself to deliver here for information Although the built-in function is much simpler.

 

CompteNum is the cowhich contains the numerical alpha data to split

 

let

Source = fx_Bal_Tiers(Ouvre_FEC1),
#"Colonnes permutées" = Table.ReorderColumns(Source,{"CompAuxNum", "CompAuxLib", "CompteLib", "Debit", "Credit", "Solde", "CompteNum"}),
Remplace_esp_rien_Dans_NUM = Table.ReplaceValue(#"Colonnes permutées"," ","",Replacer.ReplaceText,{"CompteNum"}),
#"Type modifié" = Table.TransformColumnTypes(Remplace_esp_rien_Dans_NUM,{{"CompteNum", type text}}),
#"Added Custom" = Table.AddColumn(#"Type modifié", "Custom", each Text.PositionOfAny([CompteNum],{"A".."Z"})),
#"Type modifié1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
Nombre_Car_Chaine = Table.AddColumn(#"Type modifié1", "Longeur_CAR", each Text.Length([CompteNum])),
#"Inserted First Characters" = Table.AddColumn(Nombre_Car_Chaine, "First Characters", each Text.Start([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Type modifié2" = Table.TransformColumnTypes(#"Inserted First Characters",{{"First Characters", Int64.Type}}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié2", "test", each if [Custom] > 0 then [Custom] else [Longeur_CAR]),
#"Inserted Text Range" = Table.AddColumn(#"Colonne conditionnelle ajoutée", "Text Range", each Text.Middle([CompteNum], if [Custom] > 0 then [Custom] else [Longeur_CAR]), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"CompteNum", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Range", "Number"}})
in
#"Renamed Columns"

 

 

Philippe Muniesa

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