Forum Discussion

gvg's avatar
gvg
Post Prodigy
5 years ago
Solved

Get substring of every column in table

Hi folks,

I am looking to figure out how to get a substring of every column in a table with a variable number of columns. My code looks like this but it is not working. It is expected to take a substring beginning with the 3rd character in every column regardless how many columns the table has.

let 
  Source = myTable,
  myColumns = Table.ColumnNames(Source),
  #"Replaced Value1" = Table.ReplaceValue(Source,
      each myColumns,
      each Text.Middle(myColumns,3),
      Replacer.ReplaceValue,myColumns)
in
    #"Replaced Value1"

What am I missing here? I am not really comfortable with using each in PQ.

Thanks.

Show and Tell
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    It's vital to understand use of different each and its different evaluation contextin in different functions.

    let
    Source = myTable,
    myColumns = Table.ColumnNames(Source),
    Custom1 = List.Accumulate(myColumns, Source, (s,c) => Table.ReplaceValue(s, each Record.Field(_, c), each Text.Start(Text.From(Record.Field(_, c)), 3), Replacer.ReplaceValue, {c}))
in
    Custom1

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    It's vital to understand use of different each and its different evaluation contextin in different functions.

    let
    Source = myTable,
    myColumns = Table.ColumnNames(Source),
    Custom1 = List.Accumulate(myColumns, Source, (s,c) => Table.ReplaceValue(s, each Record.Field(_, c), each Text.Start(Text.From(Record.Field(_, c)), 3), Replacer.ReplaceValue, {c}))
in
    Custom1
    • gvg's avatar
      gvg
      Post Prodigy
      5 years ago

      Yes, this works. Thank you !

      What would you recommend to read about each? I find Microsoft documentation being very sparse.