Record.Field Error

[primolee]

Hello everyone,

I want to use the value in column M1 as column name and extract the value in that column.

= Table.AddColumn(#"Previous Step", "Custom", each if ([M1]=null) then [Cue Final Key] else [Cue Final Key]&"|"&Record.Field(_, [M1]))

However, error shows:

Expression.Error: Cannot find the column 'Period'

Funny thing is, I have another query with very similar step and the same column name, and it works.

The only difference between these two queries is that the one not working has merge step.

Does anyone have any idea what is wrong with my query?

Thank you very much in advance.

Best regards,

David

[primolee · Accepted solution]

Somehow it is not a stable function...

My solution is to add a new column with Record.FieldNames(_), then add the next new column using the above code, it will work with 2 steps...

I guess you "sometimes" need to let the system get all column names in a list first, or else the system will not recognize all column names.  Sometimes you don't need it, sometimes you do.

Hopefully Microsoft can look into this. 😞

[v-juanli-msft]

Hi @primolee 

When using a code as below:

Record.Field(previous step,"M1" )

It would retrieve values from column[M1] into a list nested as a cell of the column.

If your solution works, please use it.

Here is a video introducing how to get columns' name from columns' values dynamically.

https://community.powerbi.com/t5/Desktop/Dynamic-column-name-from-its-value/td-p/189442

Best Regards

Maggie

\ddd

[primolee]

Hello @v-juanli-msft ,

Thanks for your reply.  Unfortunately, I only need the value of the field corresponding to the same row, this is why I use Record.Field(_, [M1]), _ underscore sign instead of #"Previous Step".

I have found somewhat a solution just right before your reply.

Sometimes it works just with Record.Field(_, [M1]), sometimes I need to create a new column using Record.FieldNames(_) first then another new column with Record.Field(_,[M1]) in order to recognize all field names.  This is very strange, but anyway... (>_<)

Thank you for the help!

Best regards,

David