Iterate over each cell in a table

[MP_123]

hi,

i want to iterate on each cell in the column (don't know how many rows and column will be, but it's not complicated to get)

i want to call a function from each cell, how can i do it?

also possible to call this function on specific row (not column)

this is the function that i found here once

let

/* Function code from http://markvsql.com/2015/03/advanced-column-splitting-in-power-query/, which was written in collaboration */
/* with Curt Hagenlocher, Microsoft */
SplitByDelimiter = (table, column, delimiter) =>
let
/* Technique that turns table column into a List of characters, filters the list for the char and then counts the rows, */
/* adding 1 to get the result */
Count = List.Count(List.Select(Text.ToList(Table.Column(table, column){0}), each _ = delimiter)) + 1,

/* Creates the column names for the expanded columns */
Names = List.Transform(List.Numbers(1, Count), each column & "." & Text.From(_)),

/* Converts list values to data type text */
Types = List.Transform(Names, each {_, type text}),

/* Splits the multi-value field into separate columns */
Split = Table.SplitColumn(table, column, Splitter.SplitTextByDelimiter(delimiter), Names),

/* Converts the new columns to data type text */
Typed = Table.TransformColumnTypes(Split, Types)
in
Typed,

thanks!

[v-shex-msft]

Hi @MP_123,

I find a simple function to loop the table, you can take a look at below formula:

let
    Custom = (source as table) =>
    let
        FunctionToApply = (x as any) =>let result= x+1 in result,
        TransformList = List.Transform(Table.ColumnNames(source), each {_ , FunctionToApply}),
        Output = Table.TransformColumns(source, TransformList)
    in
        Output
in    
    Custom 

Reference link:

Applying a Function to Every Cell in a Table in Data Explorer (Power Query)

Notice, you can modify the "x+1" part to other functions.

Regards,

Xiaoxin Sheng

[MP_123]

hi, thank you @v-shex-msft!!

i want to iterate on each cell and to split the cell by delimiter (i don't know the columns i want to split in advance)

can you please help me? i'm a bit complicated with the splitter function

thank you very much

[v-shex-msft · Accepted solution]

Hi @MP_123,

Perhaps you can try to write a function to check the table columns which contain the specified delimiter.

For example:

let
    ContainColumn= (sourcetable as table, delimiter as text) =>
    let
        ColumnName= Table.ColumnNames(sourcetable),
        result= List.Transform(ColumnName, each if List.Count(List.Select(Table.Column(sourcetable , _ ),each try Text.Contains(_ ,delimiter) otherwise null))>0 then _ else null),
        RemoveNull=List.RemoveNulls(result)
    in
        RemoveNull
in
    ContainColumn

Invoke:

Regards,

Xiaoxin Sheng