Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 .
Waiting for quick reply...
Please help me out to do this.
Thanks in advance..!!
Solved! Go to Solution.
Hi @ppatil ,
Steps:
1. Add a Custom column to Remove all letters :
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"
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.
Hi @ppatil ,
Steps:
1. Add a Custom column to Remove all letters :
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"
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.
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"
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..!!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |