Forum Discussion
Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.
Thank you. Do I understand correctly that csv62719 is you source file. If to avoid Power Query we copy/paste data from it into the sheet in main file, here by formula create massaged range and based on it and month/year/monthid parameters create aggregated result. Workflow is correct?
If like this
when
=LET(
range, $B$2:$G$19,
year, $J$3,
monthNumber, $J$4,
monthID, $J$5,
date, CHOOSECOLS(range, 2),
rangeId, CHOOSECOLS(range, 1),
monthRange, FILTER(range,
(EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 ))*
IF( monthID = "", 1, ( rangeId = monthID ) )
),
Id, CHOOSECOLS(monthRange, 1),
transaction, CHOOSECOLS(monthRange, 4),
type, CHOOSECOLS(monthRange, 5),
amount, CHOOSECOLS(monthRange, 6),
groupIndexes, {1,4,5},
group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),
result,VSTACK({"Id","Transaction","Type","Amount"},
HSTACK(
group,
BYROW( group,
LAMBDA(v, SUM(
FILTER(amount,
(Id = INDEX(v,1,1) )*
(transaction = INDEX(v,1,2) )*
(type = INDEX(v,1,3) )
)
) )
)
) ), result
)If ID is skipped (J5 is empty) it returns all ID for the selected month.
Please check With ID sheet attached.
When I execute the formula
I get #CALC as my output
I did an evaluate on the formula and see a #VALUE in several places
am I missing something I tried to fix but to no avail.
I have a small problem with not having the capabilities to refresh the data on a IPAD, it is imposable to change the month and get accurate results in the Excel output. I have tried to work around this and still run into the same problem, I have also requested this as an enhancement to Excel. However, it is not high on their priority list, and I can't get enough votes to get the fix to move it up on the list.
Is there any other way to get the results without having to do a refresh every time.?
- Thank you for the quick and excellent response.
With Power Query it's better to calculate it separately (Opening Balance query in attached file), result will be like
and Closing Balance is calculated as
=SUBTOTAL(109,[Amount]) + Opening_Balance[Opening Balance]I am trying to set current month's ending balance to be next month's opening balance. I have tried various different date functions (i.e.PreviousMonth) but cannot get this to work. Is this possible in PQ?
(previous months balance)
Opening Balance 2,349.73 (Current month)
Balance 3,594.20
Norman_McIlwaine , okay, thank you for the feedback.
- Thanks problem solved.
Replacement table is in the first sheet of the file, in grid.
Modify it here and Refresh All. Power Query only reads that table.
- I have been away for a while and I am just getting back into PQ again. I was trying to add a new entry in the table replacements and I find that I can not do this using PQ editor. I believe this is because when I open the table it is in preview mode. All that I can do is to view the data in the table. How can I edit the table to insert a new row ?
- Thanks for helping me out on this one. I was entering excel functions instead of M code functions. I must get a book on M code.
Thanks again.
