none
How to convert function parameter from text to Table Column reference RRS feed

  • Question

  • Hello, please help:

    I want the M (Power Query) function below to return a list of unique values from a given column in a table. The expression, Table.SelectRows(aTable, each [ColumnName] <> null) does not evaluate properly.  I cannot figured out how to transform the parameter, ColumnName as text, into [ColumnName], Table.column reference.  Please correct my syntax or suggest a function that will make the transformation. 


    Thanks.
    Dan

    (aTable, ColumnName as text) =>let
        resultList = Table.Column
            (
            Table.SelectRows
                (
                Table.Distinct
                    (
                    aTable,{ColumnName}
                    )
                , each ([ColumnName] <> null)
                ), ColumnName
            )
    in
        resultList


    Wednesday, February 8, 2017 3:46 AM

Answers

  • For table columns, the parameter must be defined as list.
    When you invoke the function, it will let you select a table and a column.

    My suggestion for your requirement:

    (TableColumn as list) as list =>let
        resultList = List.Distinct(List.Select(TableColumn, each _ <> null))
    in
        resultList


    • Edited by MarcelBeug Wednesday, February 8, 2017 6:10 AM
    • Marked as answer by D Bliss Tuesday, February 28, 2017 2:21 PM
    Wednesday, February 8, 2017 6:10 AM
  • To me it reads as if this is more what you're after:

    (aTable, ColumnName as Text) =>
    let
        Source = List.Distinct(Table.Column(aTable, ColumnName))
    in
        Source


    Imke Feldmann TheBIccountant.com

    Wednesday, February 8, 2017 10:08 AM
    Moderator

All replies

  • For table columns, the parameter must be defined as list.
    When you invoke the function, it will let you select a table and a column.

    My suggestion for your requirement:

    (TableColumn as list) as list =>let
        resultList = List.Distinct(List.Select(TableColumn, each _ <> null))
    in
        resultList


    • Edited by MarcelBeug Wednesday, February 8, 2017 6:10 AM
    • Marked as answer by D Bliss Tuesday, February 28, 2017 2:21 PM
    Wednesday, February 8, 2017 6:10 AM
  • To me it reads as if this is more what you're after:

    (aTable, ColumnName as Text) =>
    let
        Source = List.Distinct(Table.Column(aTable, ColumnName))
    in
        Source


    Imke Feldmann TheBIccountant.com

    Wednesday, February 8, 2017 10:08 AM
    Moderator