Forum Discussion
Transforming a column with different data format
Hi everyone, Am new to Power BI and will like to know how I can transform a column with different data format using formula. I have varying data format (as below) that can be found in 1 column : ...
First step is to identify the various scenarios. Besides the column having one value and two values separated by the minus sign, are there any other expected scenarios? Does the currency sign always appear consistently? Are you ever expecting to see negative values?
Once you have sorted the scenarios you can use Power Query conditional processing (aka IF 🙂 ) and Text-to-Number functions like Number.FromText
https://docs.microsoft.com/en-us/powerquery-m/number-fromtext
to achieve your desired result.