Forum Discussion
Power Query (M) help with creation of priority function
- 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 SourceThe 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
?
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"
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.
- Greg_Deckler8 years agoCommunity Champion
- MarcelBeug8 years agoCommunity Champion
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 SourceThe 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- Anonymous8 years agoNot applicable
Thanks. I was not aware that you can add metadata. That did the job.
- Anonymous8 years agoNot applicable
The Record.FieldNames didnt work. The error returing was:
Expression.Error: We cannot convert the value "1234" to type Record. Details: Value=1234 Type=Type