Forum Discussion

MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft 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 poss...
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

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
    Icon for Microsoft Employee rankMicrosoft 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