Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 : 

  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. 

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    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.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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