cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Transform to negative numbers

Hi all!

I have, on my Excel file, earnings and expenses. Both are positive numbers.

I want to import both information to Power BI, but I want to transform expenses only, to negative numbers, so I can make proper calculations. I know I can create custumo calculations, but I think it would be better to transform the numbers to negative.

Is it possible?

Thanks!

2 ACCEPTED SOLUTIONS
Super User

If you expenses are in their own column, just choose Standard | Multiply from the Number Column area of the Transform ribbon and enter -1. Here is the M code:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31zcyMDRX0lEyNVCK1QGJWMBEDA2AQrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Multiplied Column" = Table.TransformColumns(#"Changed Type", {{"Value", each _ * -1, type number}})
in
#"Multiplied Column"```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Microsoft Employee

@efilipe

You can add a conditional column and then delete the original one.

```let
Source = Excel.Workbook(File.Contents("C:\ Transform to negative numbers.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"data", type date}, {"tipo", type text}, {"destino", type text}, {"meio", type text}, {"conta", type text}, {"valor", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "valor_updated", each if [tipo] = "Saída" then -[valor] else [valor] ),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"valor_updated", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"valor"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"valor_updated", "valor"}})
in
#"Renamed Columns"
```

Best Regards,

Herbert

10 REPLIES 10
Anonymous
Not applicable

Write a negative in your formula for a new column

For example

create a new column
in the formula bar write:
negative expenses = - expenses

New Member

hi im trying to show a positive data feed as a negaive line in a  mortgage graph that already has a negative feeds

=(Calcs!\$T\$46:\$Y\$46,Calcs!\$AA\$46:\$AF\$46,Calcs!\$AJ\$46:\$AO\$46,Calcs!\$AQ\$46:\$AV\$46,Calcs!\$AZ\$46:\$BE\$46,Calcs!\$BG\$46:\$BL\$46,Calcs!\$BP\$46:\$BU\$46,Calcs!\$BW\$46:\$CB\$46,Calcs!\$CF\$46:\$CK\$46,Calcs!\$CM\$46:\$CR\$46,Calcs!\$CV\$46:\$DA\$46,Calcs!\$DC\$46:\$DH\$46,Calcs!\$DL\$46:\$DQ\$46,Calcs!\$DS\$46:\$DX\$46,Calcs!\$EB\$46:\$EG\$46,Calcs!\$EI\$46:\$EN\$46,Calcs!\$ER\$46:\$EW\$46,Calcs!\$EY\$46:\$FD\$46,Calcs!\$FH\$46:\$FM\$46,Calcs!\$FO\$46:\$FT\$46)

i have tried *-1 and adding a minus to the front but i just get formula error messages

Helper III

Is it not a lot easier to quickly change that in the excel prior to importing them? Or have a colum in the excel table to convert them for you so you can have both columns in case you need to use it?

Microsoft Employee

@efilipe

You can add a conditional column and then delete the original one.

```let
Source = Excel.Workbook(File.Contents("C:\ Transform to negative numbers.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"data", type date}, {"tipo", type text}, {"destino", type text}, {"meio", type text}, {"conta", type text}, {"valor", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "valor_updated", each if [tipo] = "Saída" then -[valor] else [valor] ),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"valor_updated", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"valor"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"valor_updated", "valor"}})
in
#"Renamed Columns"
```

Best Regards,

Herbert

Helper IV

Hey guys! I want to thank you both for spending some time helping me out. v-haibl-msft your solution worked great! Thank you!!

Anonymous
Not applicable

I think my query is similar so I am hoping you can help me!

I have a spreadsheet with 2 tabs:

- Cost

- Revenue

The cost tab has negative values (costs) and positive values (credits). The negative numbers (costs) are much higher so the SUM total is generally negative. I want to display the total (costs and credits) per product however because all but 3 end up being a negative number they don't display in my pie chart (only where SUM is positive do they show in the visualisaion). I hope this makes sense, please let me know if not or if you need anymore info.

Going on from this I will create a formula revenue-cost to calculate profit, so I think for this reason as well the Costs total needs to be positive?

Helper IV

Hi! To explain better 🙂

I'm importing data from Excel. Entrada means Income and Saída,expense. On Excel, we're working only with positive numbers. I want to transform this data when imported to PowerBI.

So, your solution would work great, but i need an IF statement to just make that negative change if the column "tipo" = to "Saída". 🙂

Thank you for you help! 🙂

Helper IV

Hi, no... It's in a column that has positive and negatives. I have another column that says if it's a income or expenses. Can I reference the other column so it makes the multiplication by -1 or not?

Thank you!

Responsive Resident

@efilipethis worked perfectly for me. Thanks for the answer!

These forums are great.

Super User

If you expenses are in their own column, just choose Standard | Multiply from the Number Column area of the Transform ribbon and enter -1. Here is the M code:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31zcyMDRX0lEyNVCK1QGJWMBEDA2AQrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Multiplied Column" = Table.TransformColumns(#"Changed Type", {{"Value", each _ * -1, type number}})
in
#"Multiplied Column"```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors