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
