Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jmccaskill
Frequent Visitor

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 a specific character in a table
  • In these specific columns, erase all text before a specific delimeter in each row
  • Return the edited table

This is an example of what I would like my function to do to the data:

Table prior to function 

jmccaskill_0-1652895321197.png

Table after function

jmccaskill_1-1652895360813.png

 

Below is the code that I have created for a function to try to conduct these simple operations:

 

(Loop as number, Tbl as table) =>
let
    ColMax = Table.ColumnCount(Tbl),
    newTbl = if (Loop <= ColMax) and (Text.Contains(Table.ColumnNames(Tbl){Loop},"-")) then Table.TransformColumns(Tbl, {Table.ColumnNames(Tbl){Loop}, each Text.AfterDelimiter(_,": "), type text}) else Tbl,
    CurrentCol = Loop + 1,
    output = 
    if CurrentCol <= ColMax then @Function1(CurrentCol,newTbl)
    else newTbl

in
    output

 

 

Currently in the Advanced Editor screen it shows there are no syntax errors in my code, however, when I run it I get the following error:

jmccaskill_0-1652892516235.png

I need my code to be able to edit a dynamic number of columns, since the number of columns will always be changing for this specific table. The names of the columns will also change, but the ones I need to edit will always have the specific character "-" in it. If anyone has any guidance on how to solve this error it would be greatly appreciated! 

 

P.S. this is my first ever post on here, so if I did not include enough detail or uploaded something incorrectly please let me know and I will modify this post ASAP. I am new to coding in Power Query but have a background in Python, and have spent hours trying to figure out the functions and syntax to use in here with little success.

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

3 REPLIES 3
jmccaskill
Frequent Visitor

@AlexisOlson @Anonymous I appreciate both of yalls help! This did the trick😀

Anonymous
Not applicable

Not bad, @AlexisOlson ! I would have made a query like

 

Columns = List.Select(Table.ColumnNames(TableName), each Text.Contains(_, "-")),

Func = List.Repeat({"each Text.AfterDelimiter(_, ":")}, List.Count(Columns)),

Transforms = List.Zip({Columns, Func})

 

Now you can just do Table.TransformColumns(TableName, Transforms)

 

--Nate

AlexisOlson
Super User
Super User

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.