Forum Discussion
Solved
Extracting Maximum number in string
Hello, Is there a way to extract the maximum number in a string into a new column. The number represents the number of attempts the test was conducted until I got a passing result. ...
NewStep=Table.AddColumn(PreviousStepName,"Custom",each List.Count(Text.Split([StatusColumnName],","))/2)
Yes, you can use Power Query to extract the maximum number in a string and create a new column for it. Here's how:
- Open Power Query by clicking on the "Data" tab in Excel and selecting "From Table/Range."
- Select the column that contains the string with the number of attempts.
- Click on the "Add Column" tab and select "Custom Column."
- In the formula bar, enter the following formula:
=List.Max(List.Transform(Text.Split([ColumnName]," "), each try Int64.From(_)))
Replace "ColumnName" with the name of the column that contains the string with the number of attempts. This formula splits the string into individual words, then converts each word to an integer (if possible) and returns the maximum value.
- Click "OK" and a new column will be added with the maximum number of attempts.
Note: If there are non-numeric values in the string, the formula will return an error. You may need to clean the data or modify the formula to handle these cases.
Credit: ChapGPT | https://chat.openai.com/chat