Forum Discussion

Nie's avatar
Nie
Icon for Helper I rankHelper I
5 years ago
Solved

Possible to Insert Actual Excel Formula in Power Query?

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.

  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies