Possible to Insert Actual Excel Formula in Power Query?

[Nie]

Is it possible to insert an actual Excel formula with Table.AddColumn() in Power Query?

I want to generate a new table (Table B) with 3 columns, to show correlations between the columns in Table A. For example:

Table B

Fig: Table B.

I want the rows in the "coefficient" column to have the Excel formula =CORREL(INDIRECT("TableA[" & A2 & "]"), INDIRECT("TableA[" & B2 & "]")). I need to do this because some column rows in Table A are empty and I can't find a way to calculate correlations in M or DAX when there are empty rows.

[V-pazhen-msft · Accepted solution]

@Nie 

Check this blog:
Correlation coefficient - Microsoft Power BI Community

But I would recommend you to calculate the coefficient in excel then import the result table into power bi.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[lbendlin]

"I can't find a way to calculate correlations in M"

What have you tried?  Usually custom generator functions can handle scenarios like these.

Transform Column using custom function in Power Query (part 2) | Excel City