Forum Discussion
Splitting Columns without generating new column
Hii Till__ ,
I tried with the data you provided and it is working perfect.
But I think it will not work if you enter more data but the concept/logic will remain same.
Here is the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqqorFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
#"Extracted First Characters" = Table.TransformColumns(#"Changed Type", {{"Test", each Text.Start(_, 1), type text}})
in
#"Extracted First Characters"
I can help you further if you post more accurate data.
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)"}),
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.
