cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Transform to negative numbers

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"

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Microsoft
Microsoft

Re: Transform to negative numbers

@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"

 

Transform to negative numbers_1.jpg

 

Best Regards,

Herbert

View solution in original post

10 REPLIES 10
Highlighted
Super User IX
Super User IX

Re: Transform to negative numbers

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"

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper IV
Helper IV

Re: Transform to negative numbers

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!

Highlighted
Helper IV
Helper IV

Re: Transform to negative numbers

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! 🙂

 

excel.png

Highlighted
Microsoft
Microsoft

Re: Transform to negative numbers

@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"

 

Transform to negative numbers_1.jpg

 

Best Regards,

Herbert

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Transform to negative numbers

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

Highlighted
Frequent Visitor

Re: Transform to negative numbers

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? 

Highlighted
Helper II
Helper II

Re: Transform to negative numbers

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?

Responsive Resident
Responsive Resident

Re: Transform to negative numbers

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

 

These forums are great.

Highlighted
New Member

Re: how to show positive data as negative on a line graph that has other negative data feeds

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors