Forum Discussion
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.
- Anonymous5 years ago
Check this blog:
Correlation coefficient - Microsoft Power BI CommunityBut 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
- lbendlin
Super User
"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
- AnonymousNot applicable
Check this blog:
Correlation coefficient - Microsoft Power BI CommunityBut 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.