Transforming a column with different data format

[Lia]

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 : 

1. Whole value data e.g.: $4,000 of which, I will like it to remain as it is
2. Ranged data e.g.: $2700-$4900 of which, I will like to average it to => ($2,700+$4,900)/2 
3. I also have some empty cells in the same column which I'd like it remain as it is

How can I tweak a column like this in Power BI? Is there a formula in power BI? Or should I be taking multiple steps to resolve this? 

There are multiple rows for each format.

Thank you. 

[mahoneypat]

You can use List functions to get your desired result, starting with Text.Split().  Use the expression below in a Custom Column.  Also, you can paste the below M code into a blank query (paste over the existing text in Advanced Editor), to see how to do it.

= List.Average(List.Transform(Text.Split([Column1],"-"), each Number.FromText(_)))

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjExMDBQitUBsozMDQx0VUwsQfxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Average(List.Transform(Text.Split([Column1],"-"), each Number.FromText(_)))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Currency.Type}})
in
#"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dax]

Hi @Lia , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

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.