Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Transform to negative numbers

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

efilipe

Helper IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2017
01:21 PM

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!

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

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

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

Greg_Deckler

Super User IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2017
06:48 PM

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"

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

DAX Cookbook from Packt

Proud to be a Super User!

v-haibl-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2017
12:45 AM

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

Highlighted

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

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

Greg_Deckler

Super User IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-10-2017
06:48 PM

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"

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

DAX Cookbook from Packt

Proud to be a Super User!

Highlighted
##

efilipe

Helper IV

Re: Transform to negative numbers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-11-2017
04:30 AM

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
##

efilipe

Helper IV

Re: Transform to negative numbers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2017
06:29 AM

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

v-haibl-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2017
12:45 AM

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

Highlighted
##

efilipe

Helper IV

Re: Transform to negative numbers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2017
05:14 AM

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
##

hoodeamy

Frequent Visitor

Re: Transform to negative numbers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2017
06:35 AM

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
##

Jawed

Helper II

Re: Transform to negative numbers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-07-2017
11:12 AM

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?

Highlighted
##

elliotdixon

Responsive Resident

Re: Transform to negative numbers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-12-2018
10:00 PM

Highlighted
##

aussiegreens

New Member

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2018
06:30 PM

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

Announcements

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

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors

User | Count |
---|---|

387 | |

132 | |

96 | |

95 | |

90 |

Top Kudoed Authors

User | Count |
---|---|

493 | |

186 | |

181 | |

142 | |

119 |