Forum Discussion

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Aug 18, 2023

Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

I am trying to add 5 additional columns to the function but can get only a $value:   Function :   =LET(r,B1:G8,m,6,a,TAKE(r,,1),b,INDEX(r,,2),c,INDEX(r,,3),d,INDEX(r,,4),e,INDEX(r,,5),f,INDEX(r,,...
Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 07, 2023

    Norman_McIlwaine 

    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?

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Thanks problem solved.
  • Norman_McIlwaine's avatar
    Norman_McIlwaine
    Brass Contributor
    Oct 08, 2023
    Thank you for the quick and excellent response.
  • Norman_McIlwaine's avatar
    Norman_McIlwaine
    Brass Contributor
    Oct 07, 2023

    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

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 08, 2023

    Norman_McIlwaine 

    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]
  • Norman_McIlwaine's avatar
    Norman_McIlwaine
    Brass Contributor
    Nov 27, 2023

    SergeiBaklan 

     

    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.?