PQ List.Transform Question

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I have a step in my query which contains a list of field names

Code:
= {"Customer", "Product"}

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

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

I'm trying this

Code:
= 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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
Your intent is as clear as mud but anyway, I'll have a stab at it...

Code:
= 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

Well-known Member
Joined
Sep 17, 2010
Messages
611
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

Customer - Text
Customer - Key
Product - Name
Product - Key
Customer 1 Name
C1
Product 1 Name
P1
Customer 2 Name
C2
Product 2 Name
P2

<tbody>
</tbody>


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

Customer - Text
Customer - Key
Product - Name
Product - Key
Customer - Text And Key
Product - Text And Key
Customer 1 Name
C1
Product 1 Name
P1
Customer 1 Name C1
Product 1 Name P1
Customer 2 Name
C2
Product 2 Name
P2
Customer 2 Name C2
Product 2 Name P2

<tbody>
</tbody>


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)

Code:
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

Code:
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
 
Last edited:

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
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
Code:
each
is syntactic sugar
Code:
for (_) => ...
 

Forum statistics

Threads
1,148,291
Messages
5,745,884
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top