none
using Text.Replace vs. Text.AfterDelimiter as List.Accumulater aggregate function RRS feed

  • Question

  • Ehren MFST showed me how to use List.Accumulate to do lookup and replace. There is a target field, to be replaced text, "value', and a two-field table of replacement lookup values and the replacement value.  The statement is: List.Accumulate(Table.toRows(lookup},[value],(state,current) => Text.Replace(state,current{0},current{1})).  "value" is the base table of the query and "lookup" is the name of a query that accesses the lookup and replace table.  I thought that I could use Text.AfterDelimiter(state,current{0}) and end up with essentially a split function.  I get an error.  I think it is because of the different type of the return as in "text" from Text.Replace and "any" from Text.AfterDelimiter.  Also, Text.Replace doesn't create a new column, while  Text.AfterDeliminter does.  Any thoughts would be appreciated.  I can uses the Text.Replace approach and achieve what I want by replacing the current{0} with "". Thanks


    elc

    Sunday, March 8, 2020 9:28 PM

Answers

  • Thank you for your answer.  Sorry I wasn't clear. I made an example and in the process discovered my mistake.

    The behavior between Text.Replace and Text.AfterDelimiter is different when the text of interest is not found in the target text string. Text.Replace(list,old,new) returns list if old is not found in list.  Text.AfterDelimiter(list,delimiter) returns blank if delimiter is not found in list.  When using Text.AfterDelimiter as the accumulate function in List.Accumulate, the fact of a blank return must be handled.  My code is below.  Test tables can easily be constructed.

    Test code.

    // TitleSponsorResultWithLetIf

    let

    // Retrieve a table of values to be examined.  The [TitleSponsor] is the field of interest.

    // Table2 is used for reference and as a table to which the results columns from the two implementations are added. Table2 includes field [TitleSponsor].

        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

    // Syntax - List.Accumulate(List as List, seed as any, accumulate-function) as any

    // List is a table retrieved one row at a time for each each-execution.

    // seed is a field of values - [TitleSponsor]

    // accumulate is a function: by default seed is parameter 1 and List is parameter 2

    // The parameter names can be changed but for this example (seed,list)|(state,current)

    // list is a row from a two-column table, ShortTitle2Col.

    // the Table.ToRows operation on the ShortTitle2Col-query extracts two values that become current{0} and current{1}

    // The first column, current{0} is the "old" text in .Replace or the delimiter text in .AfterDelimiter

    // The second column can be used as the "new" text in .Replace.  It is not used in .AfterDelimiter

    // The Text.Replace example. 

        AddColReplace = Table.AddColumn(Source, "Replace",

        each List.Accumulate(Table.ToRows(ShortTitle2Col),[TitleSponsor],

    // the ShortTitle2Col 'replace' value may be blank to produce identical result to .AfterDelimiter

    // Similarly, current{1} in the next line maybe replaced by "" to achieve the same thing.  

        (state,current) => Text.Replace(state,current{0},current{1}))),

    // The Text.AfterDelimiter example.

        AddColAfterDelimiter = Table.AddColumn(AddColReplace, "AfterDelimiter",

        each List.Accumulate(Table.ToRows(ShortTitle2Col),[TitleSponsor],

    // The accumulate function   

        (state,current) => 

            let

                // do this one time for each pair of 'state' and 'current{0}' values

                res = Text.AfterDelimiter(state,current{0})

            in 

            // check for blank i.e current{0} not found in 'state'

            if res = "" then state else res

            )

        )

    in

        AddColAfterDelimiter

    // ShortTitle2Col

    let

    // a two-column table of text to be found and an optional replacement value

        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"value", type text}, {"replace", type text}})

    in

        #"Changed Type"


    elc

    Wednesday, March 11, 2020 2:10 PM

All replies

  • Hi elc

    This is simply incomprehensible: no paragraph, mistake the List.Accumulate statement, "I get an error" - what exact message?... difficult to figure out exactly what you want to achieve

    So please upload either:

    • A couple of picture of what you have & what you expect
    • Post a link to a file (shared on whatever file sharing service) with the same info as above
    Tuesday, March 10, 2020 9:30 AM
  • Thank you for your answer.  Sorry I wasn't clear. I made an example and in the process discovered my mistake.

    The behavior between Text.Replace and Text.AfterDelimiter is different when the text of interest is not found in the target text string. Text.Replace(list,old,new) returns list if old is not found in list.  Text.AfterDelimiter(list,delimiter) returns blank if delimiter is not found in list.  When using Text.AfterDelimiter as the accumulate function in List.Accumulate, the fact of a blank return must be handled.  My code is below.  Test tables can easily be constructed.

    Test code.

    // TitleSponsorResultWithLetIf

    let

    // Retrieve a table of values to be examined.  The [TitleSponsor] is the field of interest.

    // Table2 is used for reference and as a table to which the results columns from the two implementations are added. Table2 includes field [TitleSponsor].

        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

    // Syntax - List.Accumulate(List as List, seed as any, accumulate-function) as any

    // List is a table retrieved one row at a time for each each-execution.

    // seed is a field of values - [TitleSponsor]

    // accumulate is a function: by default seed is parameter 1 and List is parameter 2

    // The parameter names can be changed but for this example (seed,list)|(state,current)

    // list is a row from a two-column table, ShortTitle2Col.

    // the Table.ToRows operation on the ShortTitle2Col-query extracts two values that become current{0} and current{1}

    // The first column, current{0} is the "old" text in .Replace or the delimiter text in .AfterDelimiter

    // The second column can be used as the "new" text in .Replace.  It is not used in .AfterDelimiter

    // The Text.Replace example. 

        AddColReplace = Table.AddColumn(Source, "Replace",

        each List.Accumulate(Table.ToRows(ShortTitle2Col),[TitleSponsor],

    // the ShortTitle2Col 'replace' value may be blank to produce identical result to .AfterDelimiter

    // Similarly, current{1} in the next line maybe replaced by "" to achieve the same thing.  

        (state,current) => Text.Replace(state,current{0},current{1}))),

    // The Text.AfterDelimiter example.

        AddColAfterDelimiter = Table.AddColumn(AddColReplace, "AfterDelimiter",

        each List.Accumulate(Table.ToRows(ShortTitle2Col),[TitleSponsor],

    // The accumulate function   

        (state,current) => 

            let

                // do this one time for each pair of 'state' and 'current{0}' values

                res = Text.AfterDelimiter(state,current{0})

            in 

            // check for blank i.e current{0} not found in 'state'

            if res = "" then state else res

            )

        )

    in

        AddColAfterDelimiter

    // ShortTitle2Col

    let

    // a two-column table of text to be found and an optional replacement value

        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"value", type text}, {"replace", type text}})

    in

        #"Changed Type"


    elc

    Wednesday, March 11, 2020 2:10 PM