Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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"