Forum Discussion
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. My issues is that i dont always know in wich column the delimiter is going to be. sometimes its column 4, sometimes 6, sometimes 2. The ammount of columns i have is not always the same. And to add to the complexity, the column names always change depending on my query's dynamic parameter.
Im looking for a way to split all columns that contain ":" and rename those columns to the same name + Low/High.
The end result would look like that:
Here is what ive tried so far:
the last line is giving me issues:
#"SplitColumn" = Table.SplitColumn(#"Valeur remplacée", Table.ColumnNames(#"Valeur remplacée"),10,Splitter.SplitTextByDelimiter(":",QuoteStyle.Csv))
Basically im trying to reference all columns from the previous step and split by delimiter but im getting an error: Cannot convert list type into text type. How would yall go about doing that. I understand that Table.SplitColumn expects text and not a list but i dont know how i can pass on all columns dynamically
It feels like splitting several columns at once is not very user friendly.
Thanks for the help!
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:
3 Replies
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:
Adding 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.
Thx, there was 1 edge case with 2 columns with colons, this will be perfect
