Forum Discussion
Norman_McIlwaine
Aug 18, 2023Brass Contributor
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,,...
- Sep 07, 2023
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?
SergeiBaklan
Sep 11, 2023MVP
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.
Norman_McIlwaine
Sep 12, 2023Brass Contributor
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.
- Norman_McIlwaineNov 27, 2023Brass Contributor
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.?
- Norman_McIlwaineOct 08, 2023Brass ContributorThank you for the quick and excellent response.
- SergeiBaklanOct 08, 2023MVP
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_McIlwaineOct 07, 2023Brass Contributor
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
- SergeiBaklanSep 27, 2023MVP
Norman_McIlwaine , okay, thank you for the feedback.
- Norman_McIlwaineSep 27, 2023Brass ContributorThanks problem solved.
- SergeiBaklanSep 27, 2023MVP
Replacement table is in the first sheet of the file, in grid.
Modify it here and Refresh All. Power Query only reads that table.
- Norman_McIlwaineSep 27, 2023Brass Contributor
- Norman_McIlwaineSep 27, 2023Brass ContributorI 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 ?
- Norman_McIlwaineSep 12, 2023Brass ContributorThanks 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. - SergeiBaklanSep 12, 2023MVP
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.