Adjusted R squared in Query Editor?

[Anonymous]

Hi all,

Sadly I know nothing about R, but I'd like to get the intercept, coefficients, and adjusted R squared for some variables in a query that I have in the Query Editor.

I found and liked this R script: https://stackoverflow.com/questions/48796873/multiple-linear-regression-in-power-bi

However, does anyone know whether it's possible to also generate an adjusted R squared value for the dataset and get that as well as the other numbers that are coming out at the moment?

At the moment you get two columns, 'coefficients' and 'variables'. I'm wondering whether it's possible to also get another column 'adjusted R squared'.

Thanks!

[amitchandak]

@Anonymous , I checked how R return adjusted R square values. What I got from the format is the coefficient is a table and R square is additionally given below. Now When we run Script in R we have a way to get that value in a Variable. Now if you are controlling the data returned from R then you should able append that additional column.  As, it is long since played with R, I will only be able to suggest.

[Greg_Deckler]

@Anonymous I did some stuff with that statistical fit stuff in DAX but never in Power Query. But that was Pearson correlation coefficient. Probably doesn't help at all but this is it. https://community.powerbi.com/t5/Quick-Measures-Gallery/De-Seasonalized-Correlation-Coefficient/m-p/337829#M90

@mahoneypat - You're the resident scientist, got any R chops??

[mahoneypat]

I just know enough R to be dangerous, and usually just adapt code I find online.  I'll take a look for some later.  One suggestion is to do your R analysis through the R visual instead of through the query editor (so it will be responsive to slicers, etc.).  Please see this post I wrote on powerpivotpro.com a while ago with instructions on doing that.

https://powerpivotpro.com/2018/11/hijacking-the-r-visual/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

@Anonymous 

Please see the attached pbix.  I used the same data as in the example you linked.  This has 3 ways of getting to your adj R-squared value.  The first is in the query editor (see the R Summary query and table in Data View).  This one won't respond to slicers, but may be all you need.  It references the data table, so you have have both the data table and the summary statistics available for reporting.

The other two ways are variations on using the R visual to return things other than charts.  Click on each to see the R code.  I didn't add a slicer (e.g., on the Date column), but these would update with slicer changes.  Not very pretty but a good way to enable more statistics on your report (I hope Power BI catches up with Excel in this regard eventually).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[busmansholiday]

Oh, I didn't see that you did transfer a vector back to PowerBI. Very helpful.  

[busmansholiday]

Pat,

Thanks for the pbix file.Returning a graphical representation of the 'R output' does not allow us to retrieve a data table from R. Is there any way to (A) have a table in R;  (B); build a subset based on slicers;  (C), transfer that subset to R; (D) Have R do an operating on that subset; E) return some derivative object (model coefficient(s) and standard errors for example ) back to PowerBI for display?  

[Anonymous]

@mahoneypat @Greg_Deckler Sorry for taking so long to write back - I downloaded the file and have seen that the calculations are appearing as they should in the Data View but I haven't been able to test the R code on different data as yet. Our corporate installation of Power BI and R doesn't come with Broom apparently so I'm still liaising with the ICT department about that...

Anyway I'm sure that it works and thanks so much both of you for your help! Is kudos the thumbs up? If so, I've done that.