PQ List.Transform Question

[cr731]

I have a step in my query which contains a list of field names

= {"Customer", "Product"}

I want to transform this list into a list of lists, it should look like the following

     {
     {"Customer", each Text.Combine({[#"Customer - Text"], [#"Customer - Key"]}, " "), type text},
     {"Product", each Text.Combine({[#"Product - Text"], [#"Product - Key"]}, " "), type text}
     }

I'm trying this

= List.Transform(Source, each
{
_,
each Text.Combine([#_ & " - Text"]}," "),
type text
})

The second portion of the transform, which is creating a function, is giving me an error saying "Expression.SyntaxError: Invalid identifier."

How can I reference the original list item _ within a function as a list item?

 

[Cool blue]

Your intent is as clear as mud but anyway, I'll have a stab at it...

= List.Transform(Source, each
let
  Source1 = [FONT=Verdana]List.Transform(Source, [/FONT][FONT=Verdana]each Text.Combine([#_ & " - Text"]}," ")),
[/FONT][FONT=Verdana]in
  {[/FONT]  _, Source1}
)

 

[cr731]

Thanks for the response, and sorry my question was not clear.

I'm trying to create a function that takes a table and list of column names as parameters and merges columns of the source table based on the list of column names. For example, I have a source table that looks like this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer - Text
[/TD]
[TD]Customer - Key
[/TD]
[TD]Product - Name
[/TD]
[TD]Product - Key
[/TD]
[/TR]
[TR]
[TD]Customer 1 Name
[/TD]
[TD]C1
[/TD]
[TD]Product 1 Name
[/TD]
[TD]P1
[/TD]
[/TR]
[TR]
[TD]Customer 2 Name
[/TD]
[TD]C2
[/TD]
[TD]Product 2 Name
[/TD]
[TD]P2
[/TD]
[/TR]
</tbody>[/TABLE]


I want to add merged columns for the "Text" and "Key" columns to produce this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer - Text
[/TD]
[TD]Customer - Key
[/TD]
[TD]Product - Name
[/TD]
[TD]Product - Key
[/TD]
[TD]Customer - Text And Key
[/TD]
[TD]Product - Text And Key
[/TD]
[/TR]
[TR]
[TD]Customer 1 Name
[/TD]
[TD]C1
[/TD]
[TD]Product 1 Name
[/TD]
[TD]P1
[/TD]
[TD]Customer 1 Name C1
[/TD]
[TD]Product 1 Name P1
[/TD]
[/TR]
[TR]
[TD]Customer 2 Name
[/TD]
[TD]C2
[/TD]
[TD]Product 2 Name
[/TD]
[TD]P2
[/TD]
[TD]Customer 2 Name C2
[/TD]
[TD]Product 2 Name P2
[/TD]
[/TR]
</tbody>[/TABLE]


In real life, the table would have a lot more columns and I'll be doing this on many tables that have different sets of columns, so I want to be able to add all of the Text And Key columns at once rather than having to add a merged column for each one.

For the adding of multiple columns, I have this (thanks to this post: https://social.technet.microsoft.co...le-custom-columns-to-a-table?forum=powerquery)

AddColumnFunction = (table as table, addedColumns as list) as table =>
        List.Accumulate(
            addedColumns,
            table,
            (t, d) => Table.AddColumn(t, d{0}, d{1}, d{2}?)),

Here is the full code for my working test

let
    Source = [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=table"]#table[/URL] (
        type table
            [
                #"Customer - Text" = text,
                #"Customer - Key" = text,
                #"Product - Text" = text,
                #"Product - Key" = text
            ],
        {
            {"Customer 1 Name", "C1", "Product 1 Name", "P1"},
            {"Customer 2 Name", "C2", "Product 2 Name", "P2"}
        }
        ),
    AddColumnFunction = (table as table, addedColumns as list) as table =>
        List.Accumulate(
            addedColumns,
            table,
            (t, d) => Table.AddColumn(t, d{0}, d{1}, d{2}?)),
    AddedColumns = AddColumnFunction(Source,
     {
     {"Customer - Text And Key", each Text.Combine({[#"Customer - Text"], [#"Customer - Key"]}, " "), type text},
     {"Product - Text And Key", each Text.Combine({[#"Product - Text"], [#"Product - Key"]}, " "), type text}
     })

in
    AddedColumns

So the purpose of my question in this post was to create a function which lets me create this section

{"Customer - Text And Key", each Text.Combine({[#"Customer - Text"], [#"Customer - Key"]}, " "), type text},
{"Product - Text And Key", each Text.Combine({[#"Product - Text"], [#"Product - Key"]}, " "), type text}

In an easier way so I can just have the list of column names be {"Customer", "Product"} and the function will create the list of lists above, which is needed as a parameter to the AddColumnFunction.


Maybe there is an easier way altogether to solve the original problem, so any feedback is appreciated.

Thanks

 

[Cool blue]

You can re-write the function to accept a list of list of items, each element being a list containing the two column names you want to combine plus the type. Then you can use Record.Field(_, p{n}) to reference arbitrary columns in the column generators. You could extend this strategy and create a generalised generator function bearing in mind that

each

is syntactic sugar

for (_) => ...