cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

I want to get a scrollable and slicer sensible table calculated using R

Hi community, thanks in advance for reading my question.

 

I am posting this topic after many days trying to find out a solution to my issue, reading almost everything on internet that could be helpful, but unfortunately I have not been able yet to get the answer or at least the definitive clue.

 

The problem:

Basically, I created in R a script that reads from a big CSV data source table, and creates a customized summary table from it (it creates an Excel). The script is working like a charm, for example in Studio R. Now I wanted this script to be used in Power BI desktop, to get the same table but in this great reporting tool instead of Excel. I have tried these two things but have limitations:

- Using R Visual: I first import the CSV table to the model, and add to canvas the R Visual where I paste the R script. To get the table in the visual, the best way I found is the gridExtra package, and I use tableGrob() to 'plot' the calculated table. The great thing is that I can add a slicer selecting other fields from the data source CSV table, and the R calculated table in visual is sensible to this slicer. The problem is, my table is too big to show correctly in the visual. Even expanding all the visual through the canvas, it won't show correctly, as I have too many rows. Unless I have missed something, looks like the tableGrob cannot create an image that could be scrollable if it exceeds the size of the canvas...

- I then tried to use the R Script in Power Query. I load separately the CSV  to the model and after, I create a new table in Power Query and add the R script step to read the CSV table and do all the calculations to get the final summary table. This table is successfully created in Power Query and when loaded to the model, I can use a Matrix visual for example to easily show the table in the canvas, which now is scrollable of course. BUT, now I am not able to add a slicer with fields of the original CSV table that would have effect to this summary table, as I have no way to create a relation in the model between both tables.

 

I think that the R script in Power Query has more possiblities to achieve what I want, but I need to be able to somehow filter the data source of the R script dinamically from the slicer in the canvas, so that the calculated table will change accordingly.

 

Has someone an idea how I could successfully achieve that?

 

Data is sensible, so if it's really needed, I could recreate an example with another data to upload it.

 

Thanks again to the community.

Luis Pastor

0 REPLIES 0

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.