0

I am trying to convert some hard coded PowerQuery into a function so it can be reused, but am having an issue with the field-access-expression, as it is using the parameter's name rather than value.

This is what I've tried and I get the error "The column 'Col' if the table wasn't found"

let
    table = Table.FromRecords({
        [CustomerID=1, OrderID="1"],
        [CustomerID=2, OrderID="2"],
        [CustomerID=1, OrderID="3"],
        [CustomerID=3, OrderID="4"]
    }),
    Col = "OrderID",
    Source = Table.Group(
                 table,
                 {"CustomerID"},
                 {{"Orders", each Text.Combine([Col], ",")}}
in
  Source
    

Suggestions have been to use Table.Column(table, Col) but that doesn't work for the Grouping, as it just brings them all out.

1 Answer 1

0

To use it correctly, note that each ... _ the _ refers to the sub-grouped table. You can then use a text string for the column argument in the Table.Column function.

let
    table = Table.FromRecords({
        [CustomerID=1, OrderID="1"],
        [CustomerID=2, OrderID="2"],
        [CustomerID=1, OrderID="3"],
        [CustomerID=3, OrderID="4"]
    }),
    Col = "OrderID",
    Source = Table.Group(
                 table,
                 {"CustomerID"},
                 {{"Orders", each Text.Combine(Table.Column(_,Col), ","), type text}
                 })
in
  Source

enter image description here

By the way, explicitly typing your data is preferable in Power Query, so I would suggest:

let
    table = Table.FromRecords({
        [CustomerID=1, OrderID="1"],
        [CustomerID=2, OrderID="2"],
        [CustomerID=1, OrderID="3"],
        [CustomerID=3, OrderID="4"]},
        type table[CustomerID=Int64.Type, OrderID=text]),
    Col = "OrderID",
    
Source = Table.Group(
                 table,
                 {"CustomerID"},
                 {{"Orders", each Text.Combine(Table.Column(_,Col), ","), type text}
                 })
in
  Source
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.