Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Power Query (M) help with creation of priority function

Hello,   I have created a query function which I use to add a new column with a custom function to set a value of a text from different data sources (columns, manualy or entered text). this works f...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    It is rather confusing that you post your code with the name of the function as if it is part of the code.

    Edit: also confusing is the title of this topic, Power Query has no priority function.

     

    Having said that, if I understand you correctly, you want to get the name of the column that was provided when invoking the function.

     

    One solution is to search the value in the values of your table row and return the first column name where the value is found.

    Example query and function:

     

    let
    Source = #table(2,{{1,2},{3,4}}),
    #"Invoked Custom Function" = Table.AddColumn(Source, "ColumnName", each ColumnName(_, [Column1]))
in
    #"Invoked Custom Function"
    let
    Source = (TableRow as record, FieldValue as any) as text => Record.FieldNames(TableRow){List.PositionOf(Record.FieldValues(TableRow),FieldValue,Occurrence.First)}
in
    Source

     

    The only way to be absolutely certain (also with duplicate values) is to add the column name as metadata to your table values.

    Example query and function:

     

    let
    Source = #table(2,{{1,2},{3,4}}),
    MetaData = List.Accumulate(Table.ColumnNames(Source),Source, (t,c) => Table.TransformColumns(t, {{c, each _ meta [Column = c]}})),
    #"Invoked Custom Function" = Table.AddColumn(MetaData, "GetColumn", each GetColumn([Column2]))
in
    #"Invoked Custom Function"
    let
    Source = (ValueWithMeta as any) => Value.Metadata(ValueWithMeta)[Column]
in
    Source

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

?

 

Input Priority Function= (prio1 as any, prio2 as any) =>
if prio1 <>"" and prio1 <> null and prio1 <>" " then "A" 
else
if prio2 <>"" and prio2 <> null and prio2 <>" " then "B" 
else
"N/A"
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    I need it to work for more than one pair of A,Bs. This is a hard coded solution and I would need to have another function if the input would be C and D. So basically if the input is a column I need the output to be the name of the column.

    I use the same priority function for more than 10 pairs and I don't want to have 10 different functions to find which column has been selected.
      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        8 years ago

        It is rather confusing that you post your code with the name of the function as if it is part of the code.

        Edit: also confusing is the title of this topic, Power Query has no priority function.

         

        Having said that, if I understand you correctly, you want to get the name of the column that was provided when invoking the function.

         

        One solution is to search the value in the values of your table row and return the first column name where the value is found.

        Example query and function:

         

        let
    Source = #table(2,{{1,2},{3,4}}),
    #"Invoked Custom Function" = Table.AddColumn(Source, "ColumnName", each ColumnName(_, [Column1]))
in
    #"Invoked Custom Function"
        let
    Source = (TableRow as record, FieldValue as any) as text => Record.FieldNames(TableRow){List.PositionOf(Record.FieldValues(TableRow),FieldValue,Occurrence.First)}
in
    Source

         

        The only way to be absolutely certain (also with duplicate values) is to add the column name as metadata to your table values.

        Example query and function:

         

        let
    Source = #table(2,{{1,2},{3,4}}),
    MetaData = List.Accumulate(Table.ColumnNames(Source),Source, (t,c) => Table.TransformColumns(t, {{c, each _ meta [Column = c]}})),
    #"Invoked Custom Function" = Table.AddColumn(MetaData, "GetColumn", each GetColumn([Column2]))
in
    #"Invoked Custom Function"
        let
    Source = (ValueWithMeta as any) => Value.Metadata(ValueWithMeta)[Column]
in
    Source