Log In
Register
Home
August 21, 2019
Offline
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"
October 5, 2010
Offline
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
November 8, 2013
Offline
Or,
You can use the same power query loop I explained to you in your previous post: https://www.myonlinetraininghu.....ded#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
November 8, 2013
Offline
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}}))
August 21, 2019
Offline
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 !
1 Guest(s)
