Custom column to find the biggest TEXT field for each unique value of the 3rd column

[vilkku]

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

[MarcelBeug · Accepted 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

[CahabaData]

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.

[vilkku]

But this doesn't help to find the biggest number per unique value of column 1. 

[CahabaData]

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.

[MarcelBeug]

@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.

[CahabaData · Accepted solution]

@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.....

[vilkku]

Thanks a lot guys. It gives me a lot of new ideas. 

[MarcelBeug · Accepted 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

[vilkku]

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