Forum Discussion

MP_123's avatar
MP_123
Microsoft Employee
9 years ago
Solved

Iterate over each cell in a table

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!

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      MP_123
      Microsoft Employee
      9 years ago

      hi, thank you Anonymous!!

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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