Forum Discussion

Till__'s avatar
Till__
Helper I
3 years ago

Splitting Columns without generating new column

Dear Community,    I want to split serveral columns after if it changes from number to text. For example the entry is 2xyx and I only want to have the 2 left over. Test 2xyx Normaly a ne...
Need Help
Till__'s avatar
Till__
Helper I
3 years ago

Thank you for your post and your help KeyurPatel14 !

 

The columns have all the prefix of "EF-" and then an individul numeric ID. At the moment I do it quite manually like that. The text in the cells in the columns is always like that: "3) Some random Text"  3 can be any number and the text may have differ in the length. 

 

#"Split1"= Table.SplitColumn(#"Umbenannte Spalten1", "EF-1.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.1.1)"}),
#"Split2"= Table.SplitColumn(#"Split1", "EF-1.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.1.2)"}),
#"Split3"= Table.SplitColumn(#"Split2", "EF-1.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.2.1)"}),
#"Split4"= Table.SplitColumn(#"Split3", "EF-1.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.2.1)"}),
#"Split5"= Table.SplitColumn(#"Split4", "EF-1.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.2.2)"}),
#"Split6"= Table.SplitColumn(#"Split5", "EF-1.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.3.1)"}),
#"Split7"= Table.SplitColumn(#"Split6", "EF-1.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.3.2)"}),
#"Split8"= Table.SplitColumn(#"Split7", "EF-2.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.1.1)"}),
#"Split9"= Table.SplitColumn(#"Split8", "EF-2.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.1.2)"}),
#"Split10"= Table.SplitColumn(#"Split9", "EF-2.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.2.1)"}),
#"Split11"= Table.SplitColumn(#"Split10", "EF-2.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.3.1)"}),
#"Split12"= Table.SplitColumn(#"Split11", "EF-2.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.3.2)"}),
#"Split13"= Table.SplitColumn(#"Split12", "EF-3.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.1.1)"}),
#"Split14"= Table.SplitColumn(#"Split13", "EF-3.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.1.2)"}),
#"Split15"= Table.SplitColumn(#"Split14", "EF-3.1.3)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.1.3)"}),
#"Split16"= Table.SplitColumn(#"Split15", "EF-3.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.2.2)"}),
#"Split16.1"= Table.SplitColumn(#"Split16", "EF-3.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.3.2)"}),
#"Split17"= Table.SplitColumn(#"Split16.1", "EF-4.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.1.1)"}),
#"Split18"= Table.SplitColumn(#"Split17", "EF-4.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.1.2)"}),
#"Split19"= Table.SplitColumn(#"Split18", "EF-4.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.2.2)"}),
#"Split20"= Table.SplitColumn(#"Split19", "EF-4.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.3.2)"}),
#"Split21"= Table.SplitColumn(#"Split20", "EF-5.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.1.1)"}),
#"Split22"= Table.SplitColumn(#"Split21", "EF-5.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.1.2)"}),
#"Split23"= Table.SplitColumn(#"Split22", "EF-5.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.2.1)"}),
#"Split24"= Table.SplitColumn(#"Split23", "EF-5.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.2.2)"}),
#"Split25"= Table.SplitColumn(#"Split24", "EF-5.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.3.1)"}),
#"Split26"= Table.SplitColumn(#"Split25", "EF-5.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.3.2)"}),
#"Split27"= Table.SplitColumn(#"Split26", "EF-6.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.1.1)"}),
#"Split28"= Table.SplitColumn(#"Split27", "EF-6.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.1.2)"}),
#"Split29"= Table.SplitColumn(#"Split28", "EF-6.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.2.2)"}),
#"Split30"= Table.SplitColumn(#"Split29", "EF-6.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.3.1)"}),
#"Split31"= Table.SplitColumn(#"Split30", "EF-6.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.3.2)"}),

PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

this is screaming UnPivot to me....

So unpivot the columns

Then you get an atttribute coumn with the columnname value column with the value

The you only have to do the split once

I suspect the rest of you queries will be a lot easier too! If you really want the fields back, you can do a pivot. Not at my laptop, but you pivot the attribute colum and take the value column as value parameter and select no aggregation or keep all rows (i am not sure) for the pivot parameters.