Power Query (M) help with creation of priority function

[Anonymous]

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 fairly well and you may see the function below. What I want to do now is to see which value has been selected by the column name.

Input Priority Function= (prio1 as any, prio2 as any) =>
if prio1 <>"" and prio1 <> null and prio1 <>" " then prio1 
else
if prio2 <>"" and prio2 <> null and prio2 <>" " then prio2 
else
"N/A"

So now I want to make another function (see below) that tells me that A or B has been used for value. My idea is to make an identical function e.g., Input Source Function and change the return type but I don't know what to add in ???. Any idea?

Input Source Function= (prio1 as any, prio2 as any) =>
if prio1 <>"" and prio1 <> null and prio1 <>" " then ???
else
if prio2 <>"" and prio2 <> null and prio2 <>" " then ???
else
"N/A"

[Greg_Deckler]

?

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]

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.

[Greg_Deckler]

https://msdn.microsoft.com/en-us/library/mt253585.aspx

[Anonymous]

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

[MarcelBeug · Accepted solution]

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

[Anonymous]

Thanks. I was not aware that you can add metadata. That did the job.