Forum Discussion
Updating R query for all imported dataframe sets
Please give an example of two of your queries (or mock example) to see how similar the updates are. You could use query parameters and work them into your R code, so you only need to update it in one place.
Regards,
Pat
Thank you very much for your response!
Actually there is only 1 R script - here is the beginning snippet -
library("FinancialMath") #https://cran.r-project.org/web/packages/FinancialMath/FinancialMath.pdf
library("FinCal") #https://cran.r-project.org/web/packages/FinCal/FinCal.pdf
principal.amount <- 100000
number.of.periods <- 60
monthly.rate1<- 0.10/12
monthly.rate2<- 0.04/12
amortization.schedule1 <- amort.table(Loan = principal.amount,
n = number.of.periods,
i = monthly.rate1)
amortization.schedule1 <-data.frame(amortization.schedule1$Schedule)
present.value <- -pv(monthly.rate1, number.of.periods, pmt = amortization.schedule1$Payment[1])
amortization.schedule2 <- amort.table(Loan = present.value,
n = number.of.periods,
i = monthly.rate2)
amortization.schedule2 <-data.frame(amortization.schedule2$Schedule)
Now when I go to powerBI and run the script, I get the following popup and can import the dataframes by selecting them
After the import is successful, I see the gear icon for source on both of the data frames eg
This makes me feel that the data souce of both these tables is different, albeit they are generated from the same code. As I continue to build my script and import more data frames/tables, I'm confused on how do I ensure the consistency of code across all tables/frames? Do I have to change all sources one by one or can I somehow just 'update' the query once so that all tables pulled from that query are in sync?
Maybe I'm unclear on what you are trying to do, but I would approach this as follows:
1. Make a table with columns for present value, number of periods, and rate (maybe add an index column too to make filtering to single analysis on your report easier)
2. Make function with your R script in it that takes those three values as inputs
3. Add a column with Invoke Custom Function with your #2 function and then expand that table to get all your data appended together (which you could use slicers/filters/etc. to pull out what you need in visuals.
Alternatively, you could just use R in your visual and display the amortization schedule for whatever values are selected from slicers (What-If Parameters). See this post I wrote on using the R visual like this.
https://powerpivotpro.com/2018/11/hijacking-the-r-visual/
Regards,
Pat