format a column to millions

[NB3]

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,

[Greg_Deckler]

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