Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppatil
Helper III
Helper III

How to find mid point of Employee size

Hi Community..!!
I wanted to find mid point of the Employee Size column.
Please refer the below images.

As u see in the below column, the first row contain 500 - 999 employees , the mid point of this is (500+999)/2 = 749.5 , like that the 2nd row contain 20 - 49 , the mid point of this is (20+49)/2 = 34.5 . like this there are so many rows. I want mid point of every row in one go or using one formula or using one DAX .

ppatil_0-1632131133103.png

Waiting for quick reply...

Please help me out to do this.
Thanks in advance..!!

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @ppatil ,

 

Steps:

1. Add a Custom column to Remove all letters :

  • Lower case letters : Text.Remove([Employee Size],{"a".."z"}))
  • Upper case letters: Text.Remove([Employee Size],{"A".."Z"}))

2. Split the Custom column by "-"

3. Replace all null with 0

4. Add a Custom column to calculate the average.

5. Remove all unnecessary columns.

 

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUw0LW0tFRIzS3Iya9MTS1WitWJVjIy0DUhV8xQF0PEQNcQXQxorzZBjcQZbwryAemqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Employee Size],{"a".."z"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Custom.1", "Custom.2"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Custom", each ([Custom.1] +[Custom.2]) /2),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1", "Custom.2"})
in
    #"Removed Columns"

Eyelyn9_0-1632376786007.png

 

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

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

Hi @ppatil ,

 

Steps:

1. Add a Custom column to Remove all letters :

  • Lower case letters : Text.Remove([Employee Size],{"a".."z"}))
  • Upper case letters: Text.Remove([Employee Size],{"A".."Z"}))

2. Split the Custom column by "-"

3. Replace all null with 0

4. Add a Custom column to calculate the average.

5. Remove all unnecessary columns.

 

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUw0LW0tFRIzS3Iya9MTS1WitWJVjIy0DUhV8xQF0PEQNcQXQxorzZBjcQZbwryAemqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Employee Size],{"a".."z"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Custom.1", "Custom.2"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Custom", each ([Custom.1] +[Custom.2]) /2),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1", "Custom.2"})
in
    #"Removed Columns"

Eyelyn9_0-1632376786007.png

 

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

Thank u so much @v-eqin-msft 
Its solved my problem..!!

 

Regards,

Prasad Patil

selimovd
Super User
Super User

Hey @ppatil ,

 

I guess you want the middle between the from and the to number?!

I personally would do that in Power Query as it's a lot easier.

 

Try the following approach in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUw0LW0tFRIzS3Iya9MTS1WitWJVjIy0DUhV8xQF0PEQNcQXQxorzZBjcQZbwryAemqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Size", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Employee Size], " "), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Text Before Delimiter", "Text Before Delimiter", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Text Before Delimiter.1", "Text Before Delimiter.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text Before Delimiter.1", Int64.Type}, {"Text Before Delimiter.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Text Before Delimiter.1", "From"}, {"Text Before Delimiter.2", "To"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [To] = null then [From] else [To]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"To"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "To"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each ( [From] + ([To] - [From]) / 2)),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Custom", "Average"}})
in
    #"Renamed Columns2"

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous
Not applicable

What is a/the 'mid point' in this situation? What are you looking for? The mode? Can you please give a good example? Meaning: INPUT -(rule)-> OUTPUT...

Hey Daxer,
As u see in the below column, the first row contain 500 - 999 employees , the mid point of this is (500+999)/2 = 749.5 , like that the 2nd row contain 20 - 49 , the mid point of this is (20+49)/2 = 34.5 . like this there are so many rows. I want mid point of every row in one go or using one formula or using one DAX .
I hope u have understood it now..!!

ppatil_0-1632133520778.png

Need quick solution..!!

I think you mean the span, not the midpoint.  In any case, you can use this in a custom column in the query editor.

 

= Number.FromText(Text.BetweenDelimiters([Employee Size], "-", " ")) - Number.FromText(Text.BeforeDelimiter([Employee Size], "-"))

 

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


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.