Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RobinHaerkens
Frequent Visitor

Transforming Data

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.

1 ACCEPTED SOLUTION
PijushRoy
Super User
Super User

Hi @RobinHaerkens 

Create a Custom Column

PijushRoy_0-1697541186885.png

 

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

Then replace Cr to blank

PijushRoy_1-1697541247208.png


If solve, mark as SOLUTION

Thanks




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





View solution in original post

2 REPLIES 2
PijushRoy
Super User
Super User

Hi @RobinHaerkens 

Create a Custom Column

PijushRoy_0-1697541186885.png

 

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

Then replace Cr to blank

PijushRoy_1-1697541247208.png


If solve, mark as SOLUTION

Thanks




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





mlsx4
Super User
Super User

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"

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors