Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am struggling to find a solution for the custom column 3 below. I try to find the biggest text of column 2 for each unique value of column 1. My problems are:
- i can't use MAX for column 2 since it is TEXT Field. I might have a wordaround about it, but I have no clue about issue below.
- I need to identify the biggest text for each individual value in column 1. For this, I have no clue.
Column 1 | Column 2 | Column 3 |
Jenni | Forecast1 | No |
Jenni | Forecast2 | Final forecast |
Weiliang | Forecast1 | No |
Weiliang | Forecast2 | No |
Weiliang | Forecast3 | No |
Weiliang | Forecast4 | No |
Weiliang | Forecast5 | No |
Weiliang | Forecast6 | No |
Weiliang | Forecast7 | No |
Weiliang | Forecast8 | Final forecast |
Minna | Forecast1 | No |
Minna | Forecast2 | No |
Minna | Forecast3 | No |
Minna | Forecast4 | No |
Minna | Forecast5 | No |
Minna | Forecast6 | Final forecast |
Solved! Go to Solution.
A Power Query solution with a query and a function.
Query:
let Source = Table1, #"Invoked Custom Function" = Table.AddColumn(Source, "LastNumber", each fnLastNumber([Column2])), #"Grouped Rows" = Table.Group(#"Invoked Custom Function", {"Column1"}, {{"Max", each List.Max([LastNumber]), type number}, {"AllData", each _, Value.Type(#"Invoked Custom Function")}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Column2", "LastNumber"}, {"Column2", "LastNumber"}), #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Column3", each if [Max] = [LastNumber] then "Final forecast" else "No"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max", "LastNumber"}) in #"Removed Columns"
Function fnLastNumber:
This function splits a string first on each digit, then uses the resulting list of strings (without empties) as delimiters to split the String again using Splitter.SplitTextByEachDelimiter. This will result in a list with numbers (digits only), of which the last non-empty item is the last number in the string.
(String as text) as nullable number => let // String = "Forecast1", SplitOnDigits = Text.SplitAny(String,"0123456789"), Delimiters = List.Select(SplitOnDigits,each _ <> ""), SplitOnDelimiters = Splitter.SplitTextByEachDelimiter(Delimiters)(String), LastDigits = List.Last(List.Select(SplitOnDelimiters, each _ <> "")), LastNumber = try Number.From(LastDigits) otherwise null in LastNumber
@MarcelBeug you are the master of power query and M, so I'll go with your view !! to me creating a column is modeling which I think of being anything at the table level - - and not up in the visuals.....
but in any case I interpreted the original post as not able to use MAX with a text string...so was just suggesting to carve off the number characters and create a new number column.....
Use the data modeling to split off the numeric characters of column 2 into a separate column and set that as a number field so you can use the MAX function.
But this doesn't help to find the biggest number per unique value of column 1.
my post was just the suggestion to split column, so you would have the numbers of column 2 alone - and thereby have a basis to use the MAX function
this task unto itself is just a modeling step; once you have that you can craft a dax measure - or even in visuals they will offer a max option for a number value.
@CahabaData I think the crafting is in the modeling step (i.e. in Power Query).
Depending on the exact requirements, this might be all or just some simple DAX or option in a visual can be used.
@MarcelBeug you are the master of power query and M, so I'll go with your view !! to me creating a column is modeling which I think of being anything at the table level - - and not up in the visuals.....
but in any case I interpreted the original post as not able to use MAX with a text string...so was just suggesting to carve off the number characters and create a new number column.....
Thanks a lot guys. It gives me a lot of new ideas.
A Power Query solution with a query and a function.
Query:
let Source = Table1, #"Invoked Custom Function" = Table.AddColumn(Source, "LastNumber", each fnLastNumber([Column2])), #"Grouped Rows" = Table.Group(#"Invoked Custom Function", {"Column1"}, {{"Max", each List.Max([LastNumber]), type number}, {"AllData", each _, Value.Type(#"Invoked Custom Function")}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Column2", "LastNumber"}, {"Column2", "LastNumber"}), #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Column3", each if [Max] = [LastNumber] then "Final forecast" else "No"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max", "LastNumber"}) in #"Removed Columns"
Function fnLastNumber:
This function splits a string first on each digit, then uses the resulting list of strings (without empties) as delimiters to split the String again using Splitter.SplitTextByEachDelimiter. This will result in a list with numbers (digits only), of which the last non-empty item is the last number in the string.
(String as text) as nullable number => let // String = "Forecast1", SplitOnDigits = Text.SplitAny(String,"0123456789"), Delimiters = List.Select(SplitOnDigits,each _ <> ""), SplitOnDelimiters = Splitter.SplitTextByEachDelimiter(Delimiters)(String), LastDigits = List.Last(List.Select(SplitOnDelimiters, each _ <> "")), LastNumber = try Number.From(LastDigits) otherwise null in LastNumber
Hi,
I am trying to create a custom column (Column 3) below based on Column 2 for each unique value of column 1. My challenges are:
- MAX is for number field to find the biggest number, but I don't find any formula to get the biggest text field.
- I can get the biggest number for all values of a column, but I don't find a way to get the biggest number for each individual value in column 1
Column 1 | Column 2 | Column 3 |
Jenni | Forecast1 | No |
Jenni | Forecast2 | Final forecast |
Weiliang | Forecast1 | No |
Weiliang | Forecast2 | No |
Weiliang | Forecast3 | No |
Weiliang | Forecast4 | No |
Weiliang | Forecast5 | No |
Weiliang | Forecast6 | No |
Weiliang | Forecast7 | No |
Weiliang | Forecast8 | Final forecast |
Minna | Forecast1 | No |
Minna | Forecast2 | No |
Minna | Forecast3 | No |
Minna | Forecast4 | No |
Minna | Forecast5 | No |
Minna | Forecast6 | Final forecast |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |