cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NB3 Regular Visitor
Regular Visitor

format a column to millions

Hi everyone,

 

I'm facing an issue with my Power BI desktop today,

 

Basically I have 2 Excel spreadsheets, one with an actual revenu and one with the target revenue.

 

I can create a relationship between the tables and everything but in one spreadsheet numbers are written in millions ($2.900.000) and in the other spreadsheet my target is written as 218.5 (M$). So my target is invisible if I create a graph and it doesn't make any sense. 

 

I'm not allowed to change anything in the spreadsheets so I was wondering if there is a formula to convert my target to millions in Power BI ?

 

Thanks,

1 REPLY 1
Super User
Super User

Re: format a column to millions

Sure,

 

let splitColumn1 = Splitter.SplitTextByDelimiter(" (", QuoteStyle.None)([Column1]), splitsplitColumn10 = Splitter.SplitTextByDelimiter(".", QuoteStyle.None)(splitColumn1{0}?) in Text.Combine({Text.Combine(splitsplitColumn10), "00000"})

BTW, I used "Column from Examples" to get this formula, worked!!


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

Proud to be a Datanaut!