Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom Function Looping Through Columns in Table

Hi All,   Been struggling for hours to figure out why my function is not working in the Power Query Editor. A quick synopsis of what I am trying to to: Find all columns where the header contains ...
  • AlexisOlson's avatar
    4 years ago

    There's no reason to use loops here and your error is related to the Loop index. M is a functional language and works best when you define transformations rather than procedural code.

     

    For simplicity, it's often easier to define a query instead of a function that needs to be called when first building something and then turn it into a function once you have it working. For example:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFKTiwCkulFicXFQLqsKD8/10rBFCQBFMqwUjAyALJLUotLrBRMlGJ1opUKUsE6SopKk7PBdCrImKTEvHQrBXMgqzi7CKjWECIGhFYKFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, #"change-1" = _t, #"change-2" = _t, #"change-3" = _t]),
        ColsToTransform = List.Select(Table.ColumnNames(Source), each Text.Contains(_, "-")),
        TransformDefinition = List.Transform(ColsToTransform, each {_, each Text.AfterDelimiter(_, ": "), Int64.Type}),
        TransformColumns = Table.TransformColumns(Source, TransformDefinition)
    in
        TransformColumns

    This is a dynamic version of the following:

    let
        Source = Table.FromRows([...]),
        #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"change-1", each Text.AfterDelimiter(_, ": "), Int64.Type}, {"change-2", each Text.AfterDelimiter(_, ": "), Int64.Type}, {"change-3", each Text.AfterDelimiter(_, ": "), Int64.Type}})
    in
        #"Extracted Text After Delimiter"

     

    You can turn the dynamic version into a function like this:

    (Tbl as table) as table =>
    let
        ColsToTransform = List.Select(Table.ColumnNames(Tbl), each Text.Contains(_, "-")),
        TransformDefinition = List.Transform(ColsToTransform, each {_, each Text.AfterDelimiter(_, ": "), Int64.Type}),
        TransformColumns = Table.TransformColumns(Tbl, TransformDefinition)
    in
        TransformColumns

    You can then call this function on any table or within a query. Once the above function is defined as fn_TranformColumns, you can rewrite the first query I wrote as this:

    let
        Source = Table.FromRows([...]),
        InvokeFunction = fn_TransformColumns(Source)
    in
        InvokeFunction

     

     

    Note: For future posts, please provide your sample data in a format that can easily be copied & pasted.