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

[Norman_McIlwaine]

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,,6),g,INDEX(r,7),u,SORT(UNIQUE(a)),HSTACK(u,MMULT(--(TOROW(a)=u),(MONTH(b)=m)*c)))

Data
A                       B                      C           D          E                                     F                                     G
Chequing 06962-5001375 2023-08-01 " " INSURANCE IND ALL AC-EST                                  -203.64
Chequing 06962-5001375 2023-08-01 " " INSURANCE THE PERSONAL                                   -269.88
Chequing 06962-5001375 2023-08-02 " " OD HANDLING FEE 1 @ $ 5.00                                        -5
Chequing 06962-5001375 2023-08-09 " " Transfer WWW TRANSFER - 0982                           1000.00
Chequing 06962-5001375 2023-08-09 " " Payment WWW PAYMENT - 5925 MILTON - TAXE         -50
Chequing 06962-5001375 2023-08-09 " " Payment WWW PAYMENT - 2317 RELIANCE                  -55
Chequing 06962-5001375 2023-08-09 " " Payment WWW PAYMENT - 8635 MILTON HYDRO -145.52
Chequing 06962-5001367 2023-04-18 OD INTEREST -3.7

Result #VALUE

If I use a subscript of the data i.e. 3 col The function is of:

Function:

=LET(r,A1:G8,m,6,a,TAKE(r,,1),b,INDEX(r,,2),c,INDEX(r,,3),u,SORT(UNIQUE(a)),HSTACK(u,MMULT(--(TOROW(a)=u),(MONTH(b)=m)*c)))

Data:

06962-5001375 2013-06-10 2.00
06962-5001375 2013-06-10 3.00
06962-5001375 2013-05-13 4.00
06962-5001375 2013-05-14 5.00
06962-5001375 2013-06-15 6.00
06962-5001380 2013-06-17 7.00
03092-5224710 2013-06-17 8.00
03092-5224710 2013-06-18 9.00

Result:

06962-50011121 3
06962-50011122 3
06962-50011123 6

Thanks tp P. b. at stack overflow for the original suggestion.

How do I add in the extra columns?

I would appreciate any help that you can provide me

Thanking you in advance.

[#2]

@Norman_McIlwaine 

Preliminary I added formula into Report sheet.

[#3]

@SergeiBaklan 

Thank you for looking at this for me, It does look good however I have noticed a few of the items that I require are not in the output and should have been moved from columns 5 to 6.
PENSON,
OLD AGE SECURITY
CPP,
PROV/LOCAL GOV PYMT,
ESSO CIRCLE K
OD INTEREST

What I tries to do in Power Query was to remove all the entries that contain "IDP" in both columns by using the find and replace function. But I could never use a wild card like *IDP* to remove all the entries. Then I can move the above entries to the corresponding columns without any fear of losing important data. Then I can remove the columns that I do not require.
How to do this in Power Query I do not know. could you send me the Power Query inputs to achieve this output for my perusal then I might understand how power query works. I will send you the latest download to use.

[#4]

@Norman_McIlwaine 

Tried to correct, please check in attached if I understood the logic correctly.

Query is within the file. On ribbon Data->Queries and Connections. Double click on right pane on query name to open it in Power Query Editor. Within it you may use Home->Advanced Editor to play with generated M-code.

[#5]

Thanks for the quick response, I will give a try.

[#6]

@Norman_McIlwaine 

It is looking very good and I am able to follow the M source for Transaction changes on the csv file. However i cannot see how the report is structured in the source code. If for instance, rather than type in and remember a16 digit account numbers I could have a pick box attached to the id field of the four account numbers. similar to the pick box I added to the Excel output.

The Total Income and Total expenses (which was a great after thought thank you) was a nice surprise in the report.

PETRO CANADA AND Esso are Gasoline and should be summed together if possible? 

The Source in PQ is showing Petro Canada as 29.47 and the report shows 49.05 with only one Petro Canada entry in the Transaction field for the month = 8  could I be missing something in the source file for the month = 8.

If I could see the source for the report I perhaps could help.

Thanking you for any assistance.

[#7]

Sorry Sergei, Of course the report is after you pass the transaction from PQ to excel run it through the Excel formula. I was not thinking as I am in the middle of moving houses as well as this project.

[#8]

@Norman_McIlwaine 

I added the table in first sheet for the replacements

In second sheet is the list of accounts with added empty record

it is used for drop-down list in the reporting. Didn't transfer 4.51e+15 into long number and keep it as it is. Since in csv files it could be shown as 4.51e+15, 4.5140e+15, whatever.  It looks like you open csv in Excel and save it on closing, thus account is saved as Excel transforms it.

Not critical, but to be safe better not to open csv in Excel such way. Better to allow legal connector in options

With that in new Excel file you may insert csv as Data->Get data->Legacy Wizard->From text and on third step set data type as Text for Account column

Above is assuming if 45000... was not converted on 4e+15 before.

Finally in Reports sheet added one returned by Power Query

you only need to refresh it if parameters were changed.

[#9]

@SergeiBaklan 

Thank you very much for the enhancements it looks fabulous. I have one problem which I have been working on all day.

Its how do I do a removal on a transaction without the use of the * or ? in a find and replace situation.  

In Excel I could do a find and replace with find PAYMENT???????? replace with XXXXXXXXXXXXX.

Unfortunately without the use of * or ? in PQ it is difficult and I have tried some work arounds but all failed with errors. 

I would like to change the transaction  PAYMENT - XXXX with nothing leaving the remainder of the text in the field where this text appears in the transaction field.

Example

If transaction = PAYMENT - 1155 NEO FINANCIAL replace with NEO FINANCIAL.

The numeric value changes on every occurrence therefor, I tried to remove the first 15 characters PQ and got into a lot of errors. What would be your work around this statement?

If you could help me out I would appreciate it very much.

[#10]

@Norman_McIlwaine 

I added

    fnPayment = (str as text) =>
        Text.Trim(
            Text.Combine(
                List.Skip(
                    Splitter.SplitTextByCharacterTransition({"0".."9"}, {" "} )(str),
                (q) => Text.StartsWith(q, "PAYMENT -") and Text.Length(q) = 14 )
        ) ),

into the body of query and replace texts with help of it.

[#11]

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.

[#12]

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 ?

[#13]

@Norman_McIlwaine 

[#14]

@Norman_McIlwaine 

Replacement table is in the first sheet of the file, in grid.

Modify it here and Refresh All. Power Query only reads that table.

[#15]

Thanks problem solved.

[#16]

@Norman_McIlwaine , okay, thank you for the feedback.

[#17]

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

[#18]

@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]

[#19]

Thank you for the quick and excellent response.

[#20]

@Norman_McIlwaine 

HI

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

[#21]

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