Make a Correlation Table with Power Query

I previously made a function to make a Pearson correlation calculation. I was doing some Python and Power BI training and was using the Pandas corr() function to make a correlation matrix. It’s a simple one-liner in Python. Could a make a function in Power Query that does the same?

The Code

With a nested let() function, I reused the function I made before, with a few slight changes (converting list items back to numbers). After that, I was a lot of finagling to get it right. Using Table.Schema() allowed me to select all columns of number type automatically and include them in transformations. Pivoting towards the end helped me get it in the right shape and a few cleanup steps after that got me a table.

Link to gist: fnCorrMatrix.pq (github.com)

Testing It Out

I used a table from this website: How to Read a Correlation Matrix (statology.org)

Tip: Save the image and use Excel’s get data from image functionality.

Once I did that and a little cleanup, I imported the table into Power Query and applied the function:

Closing Thoughts

Another useful function for exploratory data analysis in the books. I am thinking about making a Power Query extension with a lot of the functions I have been writing and this will probably be one of them. It seemed hard at the start, but attacking the problem step-by-step made it simpler, as always.