SOLVED

Powerquery keep just substring

%3CLINGO-SUB%20id%3D%22lingo-sub-1513393%22%20slang%3D%22en-US%22%3ERe%3A%20Powerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723876%22%20target%3D%22_blank%22%3E%40CareLess%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%20done%20from%20user%20interface%20without%20coding%3A%3C%2FP%3E%0A%3CP%3E-%20split%20column%20by%20right%20most%20opening%20bracket%3C%2FP%3E%0A%3CP%3E-%20split%20second%20column%20by%20left%20most%20opening%20bracket%3C%2FP%3E%0A%3CP%3E-%20remove%20other%20but%20middle%20column%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGenerate%20script%20will%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Split%20Column%20by%20Delimiter%22%20%3D%20Table.SplitColumn(Source%2C%20%22A%22%2C%20Splitter.SplitTextByEachDelimiter(%7B%22%7B%22%7D%2C%20QuoteStyle.Csv%2C%20true)%2C%20%7B%22A.1%22%2C%20%22A.2%22%7D)%2C%0A%20%20%20%20%23%22Split%20Column%20by%20Delimiter1%22%20%3D%20Table.SplitColumn(%23%22Split%20Column%20by%20Delimiter%22%2C%20%22A.2%22%2C%20Splitter.SplitTextByEachDelimiter(%7B%22%7D%22%7D%2C%20QuoteStyle.Csv%2C%20false)%2C%20%7B%22A.2.1%22%2C%20%22A.2.2%22%7D)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%23%22Split%20Column%20by%20Delimiter1%22%2C%7B%22A.2.1%22%7D)%0Ain%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1512459%22%20slang%3D%22en-US%22%3EPowerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512459%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20Have%20records%20with%20a%20string%20that%20can%20have%201%20ore%20more%20substring%20in%20surrounded%20by%20brackets.%3C%2FP%3E%3CP%3EI%20just%20want%20those%20substrings%2C%20the%20rest%20i%20want%20to%20toss.%3C%2FP%3E%3CP%3EAny%20handy%20tips%20or%20functions%20i%20can%20use%3F%3CBR%20%2F%3E%26nbsp%3BIn%20vba%20it's%20simple%3A%3CBR%20%2F%3E%5BQuick%20and%20dirty%20looping%20through%20the%20whole%20string%5D%3C%2FP%3E%3CP%3EFunction%20Return_PartialString(ByVal%20ls_string%20As%20String)%20As%20String%3CBR%20%2F%3E'simple%20loop%20through%20string%20and%20get%20what%20we%20want%20from%20it.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E'decl%20vars%0ADim%20lb_wanted%20As%20Boolean%0ADim%20ls_newstring%20As%20String%0A%0A'init%20vars%0Alb_wanted%20%3D%20False%0Als_newstring%20%3D%20%22%22%0A%0AFor%20i%20%3D%201%20To%20Len(ls_string)%0A%0AIf%20lb_wanted%20%3D%20True%20Then%0Als_newstring%20%3D%20ls_newstring%20%26amp%3B%20Mid(ls_string%2C%20i%2C%201)%0AEnd%20If%0A%0AIf%20Mid(ls_string%2C%20i%2C%201)%20%3D%20%22%7B%22%20Then%0Alb_wanted%20%3D%20True%0AEnd%20If%0A%0AIf%20Mid(ls_string%2C%20i%2C%201)%20%3D%20%22%7D%22%20Then%0Alb_wanted%20%3D%20False%0AEnd%20If%0A%0ANext%20i%0Als_newstring%20%3D%20Replace(ls_newstring%2C%20%22%7D%22%2C%20%22%20%22)%0A%0AReturn_PartialString%20%3D%20ls_newstring%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%5BSlightly%20more%20neat%20using%20a%20split%20function%5D%0A%0AFunction%20Return_PartialString_Strict(ByVal%20ls_string%20As%20String)%20As%20String%0A'simple%20loop%20through%20string%20and%20get%20what%20we%20want%20from%20it.%0A%0A'decl%20vars%0ADim%20ls_newstring%20As%20String%0ADim%20las_string()%20As%20String%0A%0A'init%20vars%0Als_newstring%20%3D%20%22%22%0Alas_string%20%3D%20Split(ls_string%2C%20%22%7D%22)%0A%0AFor%20i%20%3D%20LBound(las_string)%20To%20UBound(las_string)%0AIf%20InStr(1%2C%20las_string(i)%2C%20%22%7B%22%2C%20vbTextCompare)%20%26gt%3B%200%20Then%0Als_newstring%20%3D%20Replace(ls_newstring%20%26amp%3B%20Mid(las_string(i)%2C%20InStr(1%2C%20las_string(i)%2C%20%22%7B%22%2C%20vbTextCompare)%20%2B%201)%2C%20%22%20%22%2C%20%22_%22)%20%26amp%3B%20%22%23%22%0AEnd%20If%0ANext%20i%0A%0Als_newstring%20%3D%20Replace(ls_newstring%2C%20%22%23%22%2C%20%22%20%22)%0A%0A%0A%0AReturn_PartialString_Strict%20%3D%20ls_newstring%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20there%20is%20something%20like%20this%20i%20can%20do%20in%20power%20query%3F%20Other%20then%20dozens%20of%20nested%20if%20you%20escape%20when%20there%20are%20none%20left%20or%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20Put%20both%20into%20a%20code%20block%20for%20more%20readability.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512459%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514183%22%20slang%3D%22en-US%22%3ERe%3A%20Powerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20responded%2C%20but%20i%20don't%20see%20it%20appear..%20so%20another%20response%20than...%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EI%20found%20a%20function%20in%20M%20to%20do%20it%2C%20if%20your%26nbsp%3B%20solution%20does%20the%20same%20it's%20probably%20easier%20and%20maybe%20performs%20better.%20I%20will%20check%20it%20out%20when%20i%20have%20the%20time%20and%20i'll%20let%20you%20know.%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%20func%20%3D%20%20%20%0A%20(InputText%20as%20text%2C%20StartDelimiter%20as%20text%2C%20EndDelimiter%20as%20text%2C%20optional%20Alternative_Dummy%20as%20text)%20%3D%26gt%3B%0A%0Alet%0A%20%20%20%20%2F%2F%20If%20the%20end%20delimiter%20contains%20the%20same%20characters%20than%20the%20start%20delimiter%20then%20that%20portion%20has%20to%20be%20replaced%20in%20the%20end%20parameter%20by%20a%20dummy.%20Otherwise%20the%20query%20fails.%0A%20%20%20%20AlternativeDummy%20%3D%20Alternative_Dummy%2C%0A%20%20%20%20Dummy%20%3D%20if%20AlternativeDummy%20is%20null%20then%20%22*%2B*%22%20else%20AlternativeDummy%2C%0A%20%20%20%20String%20%3D%20InputText%2C%0A%20%20%20%20ParaStart%20%3D%20StartDelimiter%2C%0A%20%20%20%20ParaEnd%20%3D%20EndDelimiter%2C%0A%20%20%20%20%0A%20%20%20%20%2F%2F%20If%20the%20delimiters%20are%20identical%2C%20a%20different%20logic%20has%20to%20be%20applied.%0A%20%20%20%20IdenticalDelimiters%20%3D%20ParaEnd%20%3D%20ParaStart%2C%0A%20%20%20%20%2F%2F%20Start%20of%20logic%20for%20identical%20delimiters%0A%20%20%20%20SplitText%20%3D%20Text.Split(String%2C%20ParaStart)%2C%0A%20%20%20%20ResultIdenticalDelimiters%20%3D%20List.Alternate(SplitText%2C1%2C1)%2C%0A%20%20%20%20%0A%20%20%20%20%2F%2F%20Start%20of%20logic%20for%20different%20start-%20and%20end-delimiters%3A%20Create%20dummy%20end-delimiter.%0A%20%20%20%20DummyParaEnd%20%3D%20Text.Replace(ParaEnd%2C%20ParaStart%2C%20Dummy)%2C%0A%20%20%20%20DummyString%20%3D%20Text.Replace(String%2C%20ParaEnd%2C%20DummyParaEnd)%2C%0A%20%20%20%20SplitByParaStart%20%3D%20List.Skip(Text.Split(DummyString%2C%20ParaStart)%2C1)%2C%0A%20%20%20%20ConvertToTable%20%3D%20Table.FromList(SplitByParaStart%2C%20Splitter.SplitByNothing()%2C%20null%2C%20null%2C%20ExtraValues.Error)%2C%0A%20%20%20%20ExtractBeforeParaEnd%20%3D%20Table.AddColumn(ConvertToTable%2C%20%22Result%22%2C%20each%20Text.BeforeDelimiter(%5BColumn1%5D%2C%20DummyParaEnd))%2C%0A%20%20%20%20Cleanup%20%3D%20Table.RemoveColumns(ExtractBeforeParaEnd%2C%7B%22Column1%22%7D)%5BResult%5D%2C%0A%20%20%20%20%0A%20%20%20%20Result%20%3D%20if%20Text.Contains(String%2C%20ParaStart)%20and%20Text.Contains(String%2C%20ParaEnd)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20(if%20IdenticalDelimiters%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20ResultIdenticalDelimiters%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20Cleanup)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20error%20Error.Record(%20%22At%20least%20one%20of%20the%20delimiters%20is%20not%20found%20in%20the%20string%22%20)%0Ain%0A%20%20%20%20Result%20%2C%0Adocumentation%20%3D%20%5B%0ADocumentation.Name%20%3D%20%20%22%20Text.BetweenDelimitersOccAll%20%22%2C%0ADocumentation.Description%20%3D%20%22%20Returns%20%20all%20occurrances%20of%20text%20strings%20between%20a%20delimiter%20pair.%20%20%22%2C%0ADocumentation.LongDescription%20%3D%20%22%20Returns%20%20all%20occurrances%20of%20text%20strings%20between%20a%20delimiter%20pair.%20Optional%20parameter%20Alternative_Dummy%20can%20be%20used%20if%20Dummy%20shall%20not%20be%20*%2B*.%20%22%2C%0ADocumentation.Category%20%3D%20%22%20Text%20%22%2C%0ADocumentation.Source%20%3D%20%22%20https%3A%2F%2Fwp.me%2Fp6lgsG-Y5%20%22%2C%0ADocumentation.Version%20%3D%20%22%201.0%20%22%2C%0ADocumentation.Author%20%3D%20%22%20Imke%20Feldmann%3A%20www.TheBIccountant.com%3A%20https%3A%2F%2Fwp.me%2Fp6lgsG-Y5%20%20.%20%22%2C%0ADocumentation.Examples%20%3D%20%7B%5BDescription%20%3D%20%20%22%20See%20this%20blogpost%3A%20https%3A%2F%2Fwp.me%2Fp6lgsG-Y5%20%22%2C%0ACode%20%3D%20%22%20TextBetweenDelimitersOccAll(%22%22Meanwhile%20I%20prefer%20*Python!*%20to%20*R!*%22%22%2C%20%22%22*%22%22%2C%20%22%22!*%22%22)%20%22%2C%0AResult%20%3D%20%22%20%7B%22%22Python%22%22%2C%20%22%22R%22%22%7D%20%22%5D%7D%5D%0A%20%20%0A%20in%20%20%0A%20%20Value.ReplaceType(func%2C%20Value.ReplaceMetadata(Value.Type(func)%2C%20documentation))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514172%22%20slang%3D%22en-US%22%3ERe%3A%20Powerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20answer.%20I'm%20going%20to%20try%20that%20as%20well.%3C%2FP%3E%3CP%3EI%20found%20a%20function%20to%20do%20this%2C%20but%20if%20your%20method%20works%20it's%20probably%20easier%20and%20better.%3C%2FP%3E%3CP%3EI'll%20implement%20it%20when%20I%20have%20the%20time.%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%20func%20%3D%20%20%20%0A%20(InputText%20as%20text%2C%20StartDelimiter%20as%20text%2C%20EndDelimiter%20as%20text%2C%20optional%20Alternative_Dummy%20as%20text)%20%3D%26gt%3B%0A%0Alet%0A%20%20%20%20%2F%2F%20If%20the%20end%20delimiter%20contains%20the%20same%20characters%20than%20the%20start%20delimiter%20then%20that%20portion%20has%20to%20be%20replaced%20in%20the%20end%20parameter%20by%20a%20dummy.%20Otherwise%20the%20query%20fails.%0A%20%20%20%20AlternativeDummy%20%3D%20Alternative_Dummy%2C%0A%20%20%20%20Dummy%20%3D%20if%20AlternativeDummy%20is%20null%20then%20%22*%2B*%22%20else%20AlternativeDummy%2C%0A%20%20%20%20String%20%3D%20InputText%2C%0A%20%20%20%20ParaStart%20%3D%20StartDelimiter%2C%0A%20%20%20%20ParaEnd%20%3D%20EndDelimiter%2C%0A%20%20%20%20%0A%20%20%20%20%2F%2F%20If%20the%20delimiters%20are%20identical%2C%20a%20different%20logic%20has%20to%20be%20applied.%0A%20%20%20%20IdenticalDelimiters%20%3D%20ParaEnd%20%3D%20ParaStart%2C%0A%20%20%20%20%2F%2F%20Start%20of%20logic%20for%20identical%20delimiters%0A%20%20%20%20SplitText%20%3D%20Text.Split(String%2C%20ParaStart)%2C%0A%20%20%20%20ResultIdenticalDelimiters%20%3D%20List.Alternate(SplitText%2C1%2C1)%2C%0A%20%20%20%20%0A%20%20%20%20%2F%2F%20Start%20of%20logic%20for%20different%20start-%20and%20end-delimiters%3A%20Create%20dummy%20end-delimiter.%0A%20%20%20%20DummyParaEnd%20%3D%20Text.Replace(ParaEnd%2C%20ParaStart%2C%20Dummy)%2C%0A%20%20%20%20DummyString%20%3D%20Text.Replace(String%2C%20ParaEnd%2C%20DummyParaEnd)%2C%0A%20%20%20%20SplitByParaStart%20%3D%20List.Skip(Text.Split(DummyString%2C%20ParaStart)%2C1)%2C%0A%20%20%20%20ConvertToTable%20%3D%20Table.FromList(SplitByParaStart%2C%20Splitter.SplitByNothing()%2C%20null%2C%20null%2C%20ExtraValues.Error)%2C%0A%20%20%20%20ExtractBeforeParaEnd%20%3D%20Table.AddColumn(ConvertToTable%2C%20%22Result%22%2C%20each%20Text.BeforeDelimiter(%5BColumn1%5D%2C%20DummyParaEnd))%2C%0A%20%20%20%20Cleanup%20%3D%20Table.RemoveColumns(ExtractBeforeParaEnd%2C%7B%22Column1%22%7D)%5BResult%5D%2C%0A%20%20%20%20%0A%20%20%20%20Result%20%3D%20if%20Text.Contains(String%2C%20ParaStart)%20and%20Text.Contains(String%2C%20ParaEnd)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20(if%20IdenticalDelimiters%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20ResultIdenticalDelimiters%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20Cleanup)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20error%20Error.Record(%20%22At%20least%20one%20of%20the%20delimiters%20is%20not%20found%20in%20the%20string%22%20)%0Ain%0A%20%20%20%20Result%20%2C%0Adocumentation%20%3D%20%5B%0ADocumentation.Name%20%3D%20%20%22%20Text.BetweenDelimitersOccAll%20%22%2C%0ADocumentation.Description%20%3D%20%22%20Returns%20%20all%20occurrances%20of%20text%20strings%20between%20a%20delimiter%20pair.%20%20%22%2C%0ADocumentation.LongDescription%20%3D%20%22%20Returns%20%20all%20occurrances%20of%20text%20strings%20between%20a%20delimiter%20pair.%20Optional%20parameter%20Alternative_Dummy%20can%20be%20used%20if%20Dummy%20shall%20not%20be%20*%2B*.%20%22%2C%0ADocumentation.Category%20%3D%20%22%20Text%20%22%2C%0ADocumentation.Source%20%3D%20%22%20https%3A%2F%2Fwp.me%2Fp6lgsG-Y5%20%22%2C%0ADocumentation.Version%20%3D%20%22%201.0%20%22%2C%0ADocumentation.Author%20%3D%20%22%20Imke%20Feldmann%3A%20www.TheBIccountant.com%3A%20https%3A%2F%2Fwp.me%2Fp6lgsG-Y5%20%20.%20%22%2C%0ADocumentation.Examples%20%3D%20%7B%5BDescription%20%3D%20%20%22%20See%20this%20blogpost%3A%20https%3A%2F%2Fwp.me%2Fp6lgsG-Y5%20%22%2C%0ACode%20%3D%20%22%20TextBetweenDelimitersOccAll(%22%22Meanwhile%20I%20prefer%20*Python!*%20to%20*R!*%22%22%2C%20%22%22*%22%22%2C%20%22%22!*%22%22)%20%22%2C%0AResult%20%3D%20%22%20%7B%22%22Python%22%22%2C%20%22%22R%22%22%7D%20%22%5D%7D%5D%0A%20%20%0A%20in%20%20%0A%20%20Value.ReplaceType(func%2C%20Value.ReplaceMetadata(Value.Type(func)%2C%20documentation))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516549%22%20slang%3D%22en-US%22%3ERe%3A%20Powerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723876%22%20target%3D%22_blank%22%3E%40CareLess%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20I%20misunderstood%20your%20requirements%2C%20initial%20code%20returns%20only%20very%20internal%20string.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20all%20substrings%20are%20to%20be%20returned%2C%20you%20may%3C%2FP%3E%0A%3CP%3E1)%20Use%20almost%20UI%20only%20solutions%20-%20split%20using%20variant%20of%20splitter%20with%20Any%2C%20list%20delimiters%20%7B%22%7B%22%2C%22%7D%22%7D%20and%20with%20some%20gap%20on%20number%20of%20columns%3B%20unpivot%20all%20columns%20of%20the%20result%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Use%20formula%20which%20splits%20all%20texts%20into%20the%20lists%20and%20expand%20them%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Table1%2C%0A%20%20%20%20splitTexts%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20List.RemoveMatchingItems(Text.SplitAny(%5BA%5D%2C%22%7B%7D%22)%2C%7B%22%22%7D)%0A%20%20%20%20)%2C%0A%20%20%20%20expandSubstrings%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20splitTexts%2C%0A%20%20%20%20%20%20%20%20%22Custom%22)%0Ain%0A%20%20%20%20expandSubstrings%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3)%20Use%20Imke's%20function%20which%20do%20practically%20the%20same%2C%20but%20is%20more%20universal%20solution%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAll%203%20variants%20are%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516557%22%20slang%3D%22en-US%22%3ERe%3A%20Powerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516557%22%20slang%3D%22en-US%22%3EThanks.%20I%20should%20have%20formulated%20it%20better.%20Thanks%20for%20all%20the%20effort.%3CBR%20%2F%3E%3CBR%20%2F%3EKind%20reg.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516574%22%20slang%3D%22en-US%22%3ERe%3A%20Powerquery%20keep%20just%20substring%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723876%22%20target%3D%22_blank%22%3E%40CareLess%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hey all,

 

I Have records with a string that can have 1 ore more substring in surrounded by brackets.

I just want those substrings, the rest i want to toss.

Any handy tips or functions i can use?
 In vba it's simple:
[Quick and dirty looping through the whole string]

Function Return_PartialString(ByVal ls_string As String) As String
'simple loop through string and get what we want from it.

'decl vars
Dim lb_wanted As Boolean
Dim ls_newstring As String

'init vars
lb_wanted = False
ls_newstring = ""

For i = 1 To Len(ls_string)

If lb_wanted = True Then
ls_newstring = ls_newstring & Mid(ls_string, i, 1)
End If

If Mid(ls_string, i, 1) = "{" Then
lb_wanted = True
End If

If Mid(ls_string, i, 1) = "}" Then
lb_wanted = False
End If

Next i
ls_newstring = Replace(ls_newstring, "}", " ")

Return_PartialString = ls_newstring
End Function

 

[Slightly more neat using a split function]

Function Return_PartialString_Strict(ByVal ls_string As String) As String
'simple loop through string and get what we want from it.

'decl vars
Dim ls_newstring As String
Dim las_string() As String

'init vars
ls_newstring = ""
las_string = Split(ls_string, "}")

For i = LBound(las_string) To UBound(las_string)
If InStr(1, las_string(i), "{", vbTextCompare) > 0 Then
ls_newstring = Replace(ls_newstring & Mid(las_string(i), InStr(1, las_string(i), "{", vbTextCompare) + 1), " ", "_") & "#"
End If
Next i

ls_newstring = Replace(ls_newstring, "#", " ")



Return_PartialString_Strict = ls_newstring
End Function

 

I am hoping there is something like this i can do in power query? Other then dozens of nested if you escape when there are none left or something.

 

Edit: Put both into a code block for more readability.

6 Replies
Highlighted

@CareLess 

It could be done from user interface without coding:

- split column by right most opening bracket

- split second column by left most opening bracket

- remove other but middle column

 

Generate script will be like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "A", Splitter.SplitTextByEachDelimiter({"{"}, QuoteStyle.Csv, true), {"A.1", "A.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "A.2", Splitter.SplitTextByEachDelimiter({"}"}, QuoteStyle.Csv, false), {"A.2.1", "A.2.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter1",{"A.2.1"})
in
    #"Removed Other Columns"
Highlighted

@Sergei Baklan 

Thanks for your answer. I'm going to try that as well.

I found a function to do this, but if your method works it's probably easier and better.

I'll implement it when I have the time. Thank you!

 

let func =   
 (InputText as text, StartDelimiter as text, EndDelimiter as text, optional Alternative_Dummy as text) =>

let
    // If the end delimiter contains the same characters than the start delimiter then that portion has to be replaced in the end parameter by a dummy. Otherwise the query fails.
    AlternativeDummy = Alternative_Dummy,
    Dummy = if AlternativeDummy is null then "*+*" else AlternativeDummy,
    String = InputText,
    ParaStart = StartDelimiter,
    ParaEnd = EndDelimiter,
    
    // If the delimiters are identical, a different logic has to be applied.
    IdenticalDelimiters = ParaEnd = ParaStart,
    // Start of logic for identical delimiters
    SplitText = Text.Split(String, ParaStart),
    ResultIdenticalDelimiters = List.Alternate(SplitText,1,1),
    
    // Start of logic for different start- and end-delimiters: Create dummy end-delimiter.
    DummyParaEnd = Text.Replace(ParaEnd, ParaStart, Dummy),
    DummyString = Text.Replace(String, ParaEnd, DummyParaEnd),
    SplitByParaStart = List.Skip(Text.Split(DummyString, ParaStart),1),
    ConvertToTable = Table.FromList(SplitByParaStart, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExtractBeforeParaEnd = Table.AddColumn(ConvertToTable, "Result", each Text.BeforeDelimiter([Column1], DummyParaEnd)),
    Cleanup = Table.RemoveColumns(ExtractBeforeParaEnd,{"Column1"})[Result],
    
    Result = if Text.Contains(String, ParaStart) and Text.Contains(String, ParaEnd) 
                then (if IdenticalDelimiters 
                        then ResultIdenticalDelimiters 
                        else Cleanup) 
                else error Error.Record( "At least one of the delimiters is not found in the string" )
in
    Result ,
documentation = [
Documentation.Name =  " Text.BetweenDelimitersOccAll ",
Documentation.Description = " Returns  all occurrances of text strings between a delimiter pair.  ",
Documentation.LongDescription = " Returns  all occurrances of text strings between a delimiter pair. Optional parameter Alternative_Dummy can be used if Dummy shall not be *+*. ",
Documentation.Category = " Text ",
Documentation.Source = " https://wp.me/p6lgsG-Y5 ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com: https://wp.me/p6lgsG-Y5  . ",
Documentation.Examples = {[Description =  " See this blogpost: https://wp.me/p6lgsG-Y5 ",
Code = " TextBetweenDelimitersOccAll(""Meanwhile I prefer *Python!* to *R!*"", ""*"", ""!*"") ",
Result = " {""Python"", ""R""} "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

 

Highlighted

@Sergei Baklan 

 

I responded, but i don't see it appear.. so another response than...
I found a function in M to do it, if your  solution does the same it's probably easier and maybe performs better. I will check it out when i have the time and i'll let you know. Thanks!

 

let func =   
 (InputText as text, StartDelimiter as text, EndDelimiter as text, optional Alternative_Dummy as text) =>

let
    // If the end delimiter contains the same characters than the start delimiter then that portion has to be replaced in the end parameter by a dummy. Otherwise the query fails.
    AlternativeDummy = Alternative_Dummy,
    Dummy = if AlternativeDummy is null then "*+*" else AlternativeDummy,
    String = InputText,
    ParaStart = StartDelimiter,
    ParaEnd = EndDelimiter,
    
    // If the delimiters are identical, a different logic has to be applied.
    IdenticalDelimiters = ParaEnd = ParaStart,
    // Start of logic for identical delimiters
    SplitText = Text.Split(String, ParaStart),
    ResultIdenticalDelimiters = List.Alternate(SplitText,1,1),
    
    // Start of logic for different start- and end-delimiters: Create dummy end-delimiter.
    DummyParaEnd = Text.Replace(ParaEnd, ParaStart, Dummy),
    DummyString = Text.Replace(String, ParaEnd, DummyParaEnd),
    SplitByParaStart = List.Skip(Text.Split(DummyString, ParaStart),1),
    ConvertToTable = Table.FromList(SplitByParaStart, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExtractBeforeParaEnd = Table.AddColumn(ConvertToTable, "Result", each Text.BeforeDelimiter([Column1], DummyParaEnd)),
    Cleanup = Table.RemoveColumns(ExtractBeforeParaEnd,{"Column1"})[Result],
    
    Result = if Text.Contains(String, ParaStart) and Text.Contains(String, ParaEnd) 
                then (if IdenticalDelimiters 
                        then ResultIdenticalDelimiters 
                        else Cleanup) 
                else error Error.Record( "At least one of the delimiters is not found in the string" )
in
    Result ,
documentation = [
Documentation.Name =  " Text.BetweenDelimitersOccAll ",
Documentation.Description = " Returns  all occurrances of text strings between a delimiter pair.  ",
Documentation.LongDescription = " Returns  all occurrances of text strings between a delimiter pair. Optional parameter Alternative_Dummy can be used if Dummy shall not be *+*. ",
Documentation.Category = " Text ",
Documentation.Source = " https://wp.me/p6lgsG-Y5 ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com: https://wp.me/p6lgsG-Y5  . ",
Documentation.Examples = {[Description =  " See this blogpost: https://wp.me/p6lgsG-Y5 ",
Code = " TextBetweenDelimitersOccAll(""Meanwhile I prefer *Python!* to *R!*"", ""*"", ""!*"") ",
Result = " {""Python"", ""R""} "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

 

Highlighted
Best Response confirmed by CareLess (Occasional Contributor)
Solution

@CareLess 

Perhaps I misunderstood your requirements, initial code returns only very internal string. 

If all substrings are to be returned, you may

1) Use almost UI only solutions - split using variant of splitter with Any, list delimiters {"{","}"} and with some gap on number of columns; unpivot all columns of the result

 

2) Use formula which splits all texts into the lists and expand them

let
    Source = Table1,
    splitTexts = Table.AddColumn(
        Source,
        "Custom",
        each List.RemoveMatchingItems(Text.SplitAny([A],"{}"),{""})
    ),
    expandSubstrings = Table.ExpandListColumn(
        splitTexts,
        "Custom")
in
    expandSubstrings

 

3) Use Imke's function which do practically the same, but is more universal solution

 

All 3 variants are in attached file.

Highlighted
Thanks. I should have formulated it better. Thanks for all the effort.

Kind reg.
Highlighted

@CareLess , you are welcome