Forum Discussion

Marconium2's avatar
Marconium2
Frequent Visitor
7 months ago
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. ...
  • ronrsnfld's avatar
    7 months ago

    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: