Forum Discussion
Pass Parameter Values to Calculated Table Created in M
Community ChampionSo, if you use Manage Parameters to create parameters named P, i and n then you can do this:
let
P = P,
i=i,
n = n,
Payment = P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1),
Payments = Table.FromList(
List.Generate(
()=>[Counter=0, Balance=P, MonthlyInterest=0, MonthlyPrincipal=0, MonthlyPayment=0],
each [Counter]<n,
each [ Counter=[Counter]+1,
Balance=P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+1))/(Number.Power(1+(i/12),n)-1),
MonthlyInterest = (i/12)*Balance,
MonthlyPrincipal = Payment-MonthlyInterest,
MonthlyPayment = Payment],
each [[Counter], [Balance], [MonthlyInterest], [MonthlyPrincipal], [MonthlyPayment]])
, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Payments, "Column1", {"Counter", "Balance", "MonthlyInterest", "MonthlyPrincipal", "MonthlyPayment"}, {"Payment Number", "Balance", "Monthly Interest", "Monthly Principal", "Monthly Payment"})
in
#"Expanded Column1"
- Greg_Deckler
Hmmm, yeah that's a good question. How will you be sharing this report with others? To do it this way, you would need to share the PBIX file and then users could change the parameters before they refresh the query. Depending on how you want to share this, that is going to answer that question. This might have to be moved to DAX or potentially something else.
- Agreed. Is there a way to recreate this calculated table in DAX?
- Greg_Deckler
Yes, you could create 3 measures for P, i and n. Then, you could create a new table using the New Table under the Modeling tab of the ribbon. You will need to use GENERATESERIES.
I may have time to give you the exact answer later on tonight if I do not have too many beverages. I've already had several so no guarantees. But essentially, you would create a table with GENERATESERIES to create your initial table. The rest should be just adding columns to that table using the same basic math as in the Power Query code. You could refer to your measures in your code.
GENERATESERIES
https://msdn.microsoft.com/en-us/library/mt842624.aspx
Note that I also ponder whether or not a static table is really the most suitable vehicle for whatever it is that you are solving or whether a dynamic table would be better suited to the ultimate goal of what you are trying to achieve. But, I do not understand your ultimate goal, I assume it involves some version of taking over the world, but specifics would help. Unless it compromises your ultimate goal of world domination and in that case, I understand if you want to be cagey about things.