Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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"