Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Epression.Evaluate in combination with Text.Combine

Hi,   I have a function that adds a column to my table.  This column contains the concatenation of some other columns   i.e.   (myTable) => let #"Inserted Merged Column" = Table....
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    And here is a modification using a list of columns as an additional argument:

     

    (myTable as table, mergCols as list) => 
    let        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                List.Transform(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,mergCols)), 
                    each Text.From(_,"en-BE"))
                ," | "), type text),

        Result= #"Inserted Merged Column" 
    in
        Result

     

     

    If you named this : fn-CombineCols, you could call it like this in your main query:

     

        #"Added HASH" = #"fn-CombineCols"(#"Changed Type",
       {
        "Capakey", 
        "Partnumber", 
        "Name", 
        "Street", 
        "Number", 
        "Postcode", 
        "Location", 
        "Province", 
        "StartDate", 
        "EndDate"
       })

     

     

    Examining your existing code more closely, you have already type all of the columns as text. That being the case, you can remove from the function the List.Transform(...) and shorten to:

    (myTable as table, mergCols as list) => 
    let        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,mergCols))
                ," | "), type text),

        Result= #"Inserted Merged Column" 
    in
        Result

     

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    If I understand you correctly, somthing like:

     

     

     

    (myTable) => 
    let
        #"Merge List" = {"Capakey", "Name","Province"},    
        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(Record.FieldValues(Record.SelectFields(r,#"Merge List"))
                , " | "), type text),
        Result= #"Inserted Merged Column" 
    in
        Result

     

     

     

    Of course, you could add the "List" as an argument to the function.

     

    This may be more flexible, converting any numbers to text:

     

     

    (myTable) => 
    let
        #"Merge List" = {"Capakey", "Name","Province"},    
        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                List.Transform(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,#"Merge List")), 
                    each Text.From(_))
                ," | "), type text),
                
        Result= #"Inserted Merged Column" 
    in
        Result