Forum Discussion

NB3's avatar
NB3
Icon for Helper III rankHelper III
7 years ago

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,

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

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