Forum Discussion
Solved
Need Help with Dynamic SplitColumn
Hey guys so i have a table that looks like this. What im trying to do is to split the columns that contain ":" In theory this would be easy, you select the column and split by delimiter. ...
Assuming there is just a single column that contains a colon, you merely need to find that column:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JczLDQQxCAPQXpDmxiF8w9BKlP7bmMV7MbL1xDkkxCQTquv5HfMO1OKky4d0wIQagFdvm+1lB0CZUAfIalnzIhcHhI+Y0IAQ2S07sTobTEyb0PybstaNtYKF7v0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, ManaCost = _t, Damage = _t, ColdDamage = _t, ColdDPS = _t]), #"Split Column" = [a=Record.FieldValues(Source{0}), b=List.Transform(a,each Text.Contains(_,":")), c=List.PositionOf(b,true,Occurrence.First), d=Table.ColumnNames(Source){c}, e=Table.SplitColumn(Source,d,Splitter.SplitTextByDelimiter(":"),{d & "_Low", d & "_High"})][e] in #"Split Column"Sample Data:
Results:
ronrsnfld
Super User
Super UserAdding to my first answer, if there might be multiple columns containing colons, and they all need to be split, you could use something like:
let
Source = Query1,
#"Split Column" = List.Accumulate(
Table.ColumnNames(Source),
Source,
(s,c)=>if Text.Contains(Table.Column(s,c){0},":")
then Table.SplitColumn(s,c,Splitter.SplitTextByDelimiter(":"), {c & "_Low", c & "_High"}) else s)
in
#"Split Column"
My first answer is more efficient for a single column, but I this algorithm will work for multiple columns.