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
