cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lia
New Member

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. 

 

3 REPLIES 3
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


dax
Community Support
Community Support

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
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!