Forum Discussion
Custom Function Looping Through Columns in Table
- 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 TransformColumnsThis 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 TransformColumnsYou 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 InvokeFunctionNote: For future posts, please provide your sample data in a format that can easily be copied & pasted.
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.