Transforming Data

[RobinHaerkens]

Hi all,

Need some help on transforming data:

Current: 100cr
Needed: -100

So, I would like to replace values that contain "cr" at the end to negative values without "cr" at the end. Is this possible?

Many thanks in advance,

Robin.

[PijushRoy · Accepted solution]

Hi @RobinHaerkens 

Create a Custom Column

if Text.Contains([Column2], "Cr") then ("-"&[Column2]) else [Column2]

Then replace Cr to blank

If solve, mark as SOLUTION

Thanks

[mlsx4]

Hi @RobinHaerkens 

Yes, it is possible

let
    Origen = Excel.Workbook(File.Contents("C:\example.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Column", type text}}),
    #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "WithCr", each Text.Contains([Column],"cr")=true),
    #"Valor reemplazado" = Table.ReplaceValue(#"Personalizada agregada","cr","",Replacer.ReplaceText,{"Column"}),
    #"Columna condicional agregada" = Table.AddColumn(#"Valor reemplazado", "Column_sub", each if [WithCr] = true then Number.FromText([Column])*-1 else [Column]),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columna condicional agregada",{"Column", "WithCr"}),
    #"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas quitadas",{{"Column_sub", Int64.Type}})
in
    #"Tipo cambiado1"