Buffered lists with List.Buffer()

Hi imranamikhan 

Try buffering the list before List.Accumulate

let
    Source = Table.Buffer(SharePoint.Files("https://rbcom.sharepoint.com/sites/CustomerScope", [ApiVersion = 15])),
    #"PBI_Customer_Master_Tables csv_https://rbcom sharepoint com/sites/CustomerScope/Shared Documents/1  Customer Master Cleansing/3  PowerBI/0  Power BI Master Files/0  Test Data/" = 
    Source{[Name="PBI_Customer_Master_Tables.csv",#"Folder Path"="https://rbcom.sharepoint.com/sites/CustomerScope/Shared Documents/1. Customer Master Cleansing/3. PowerBI/0. Power BI Master Files/0. Test Data/"]}[Content],
    #"Imported CSV" = Csv.Document(#"PBI_Customer_Master_Tables csv_https://rbcom sharepoint com/sites/CustomerScope/Shared Documents/1  Customer Master Cleansing/3  PowerBI/0  Power BI Master Files/0  Test Data/",[Delimiter=",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer", type text}, {"Data Source", type text}, {"Customer Text", type text}, {"Data Origin", type text}, {"IAG", type text}, {"IAG Text", type text}, {"IBG", type text}, {"IBG Text", type text}, {"Trade Type Text", type text}, {"Trade Type", type text}, {"Parent Customer Code", type text}, {"Group Company Code", type text}, {"Record Timestamp", type date}}),
    #"Appended Query" = Table.Combine({#"Changed Type", DB_CustomerMasterData_History}),
    #"Removed Other Columns" = Table.SelectColumns(#"Appended Query",{"Customer", "Data Source", "Customer Text", "Data Origin", "IAG", "IAG Text", "IBG", "IBG Text", "Trade Type Text", "Trade Type", "Parent Customer Code", "Group Company Code", "Record Timestamp"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Trade Type", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type3", "Text After Delimiter", each Text.AfterDelimiter([Customer], "/"), type text),
    
    BufferedList = List.Buffer(#"Tbl_eComm"[Keyword]),

    #"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter", "eComm Keyword", each List.Accumulate
            (
                BufferedList,

                "",

                (state, current) => 
                
                    if List.Contains(Text.Split([Customer Text], " "), current, Comparer.OrdinalIgnoreCase) and state = ""
                    
                    then state & " " & current 
                    
                    else state
                
    ))
in
    #"Added Custom"

regards

Phil

I agree you should buffer outside the List.Accumulate, but re-reading your original post I believe this would perform faster in DAX.  If you can provide some example data in the two tables/columns, a column or measure expression can be suggested.

Regards,

Pat

imranamikhan Thanks for the sample data.  This DAX column expression returned all the same results as the M approach.  I called your tables Data and Keyword, so you'll need to change the expression and use your actual table names.  There is no relationship between the tables.  Runs fast on this small dataset, but please try on your actual.

NewColumn =
VAR thiscustomer = Customer[Customer Text]
VAR matchtable =
    FILTER (
        VALUES ( Keyword[Keyword] ),
        SEARCH (
            Keyword[Keyword],
            thiscustomer,
            1,
            0
        ) > 0
    )
VAR matchkeyword =
    MAXX (
        matchtable,
        Keyword[Keyword]
    )
RETURN
    IF (
        ISBLANK ( matchkeyword ),
        "#No Match",
        matchkeyword
    )

Regards,

Pat