Hi Cataline, Mynda,
Is it possible to have only one line for the following highlighted Text Before Delimiters ?
For your advise, please
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "String", "String - Copy"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"String - Copy", each Text.BeforeDelimiter(_, ".TIF"), type text}}),
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"String - Copy", each Text.BeforeDelimiter(_, ".ZIP"), type text}}),
#"Extracted Text Before Delimiter2" = Table.TransformColumns(#"Extracted Text Before Delimiter1", {{"String - Copy", each Text.BeforeDelimiter(_, ".DWG"), type text}}),
#"Extracted Text Before Delimiter3" = Table.TransformColumns(#"Extracted Text Before Delimiter2", {{"String - Copy", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Extracted Text Before Delimiter4" = Table.TransformColumns(#"Extracted Text Before Delimiter3", {{"String - Copy", each Text.BeforeDelimiter(_, "D20"), type text}}),
#"Extracted Text Before Delimiter5" = Table.TransformColumns(#"Extracted Text Before Delimiter4", {{"String - Copy", each Text.BeforeDelimiter(_, "&"), type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Extracted Text Before Delimiter5",{{"String - Copy", Text.Trim, type text}})
in
#"Trimmed Text"
Hi Chris,
You could nest them inside each other but that'd be very ugly!
Try this
= Table.SplitColumn(#"Trimmed Text", "String", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"String.1", "String.2", "String.3"})
then delete the columns of unwanted data.
see attached.
regards
Phil
Or,
You can use the same power query loop I explained to you in your previous post: https://www.myonlinetraininghub.com/excel-forum/power-query/dynamically-expand-and-change-column-types-when-new-records-added#p16382
The loop will be: = Table.TransformColumns(#"Trimmed Text",{{"String - Copy", each List.Accumulate({".DWG",".TIF",".ZIP","-","&"},_,(TransformedText,ReplacementItem)=>Text.Replace(TransformedText,ReplacementItem,""))}})
In red is the list of replacements you want to apply to each row in the column.
In pseudo code:
For each ReplacementItem in {".DWG",".TIF",".ZIP","-","&"} apply Text.Replace function
Or, you can use the loop with the entire table, and use the Replacer function:
TransformStep= List.Accumulate({".DWG",".TIF",".ZIP","-","&"},#"Trimmed Text", (TransformedTable, ReplacementItem)=>Table.ReplaceValue(TransformedTable, ReplacementItem,"",Replacer.ReplaceText,{"String - Copy"}))
This version applies the replacements to the entire column and is identical to replacing values in multiple steps.
The previous version goes through the column and applies the loop to each row.
To replicate your steps, you can use.... another version:
= List.Accumulate({".DWG",".TIF",".ZIP","-","&"},#"Trimmed Text",(TransformedTable,ReplacementItem)=> Table.TransformColumns(TransformedTable, {{"String - Copy", each Text.BeforeDelimiter(_, ReplacementItem), type text}}))
Hi Philip,
thanks for your reply and suggestion
However, seems like my excel version do not recognise Splitter.SplitTextByCharacterTransition
Expression.Error: The name 'Splitter.SplitTextByCharacterTransition' wasn't recognized. Make sure it's spelled correctly.
Catalin
Thank you some much of your two options, really useful for my future scenarios
I had tested both
= Table.TransformColumns(#"Trimmed Text",{{"String - Copy", each List.Accumulate({".DWG",".TIF",".ZIP","-","&"},_,(TransformedText,ReplacementItem)=>Text.Replace(TransformedText,ReplacementItem,""))}})
= List.Accumulate({".DWG",".TIF",".ZIP","-","&"},#"Trimmed Text",(TransformedTable,ReplacementItem)=> Table.TransformColumns(TransformedTable, {{"String - Copy", each Text.BeforeDelimiter(_, ReplacementItem), type text}}))
and my current datasets are more towards the latter
Thank you !
