#16
|
||||
|
||||
I'm stuck at the GroupedRows step of the RegsNotRenewed ThisYear. When I clicked on the Invoke Custom Function, there is no fnLastReg to invoke.
I have started from a new file, not from what you sent. |
#17
|
||||
|
||||
Then you need to create your own fnLastReg function.
The diagnostic file I sent you contains that function in the making. If you look at the code of that query you'll see I commented out the first line: (tbl)=> and changed the source to: Source = #"RegsNotRenewedThis Year" instead of: Source = tbl Before you do anything else though, you should note that the source for this query is #"RegsNotRenewedThis Year" which is the same as the one in the non-diagnostic file but with all the steps from and including the Invoked custom function step to the end deleted and one of the words 'Table' clicked upon in the grp column to expand/drill down to that one table, to use as an example for fnLastReg to work on. It's probably best to duplicate the RegsNotRenewedThis Year query and work on that. When you refresh the preview of fnLastReg you should see all the steps (because it's not a function anymore) and be able to edit what it does more easily. When fnLastReg gives the results you want, you'll have to make fnLastReg back into a function by reversing the changes made to it above. Go back to your original RegsNotRenewedThis Year to test it. |
#18
|
||||
|
||||
P45cal,
This is the code that I need to "reverse" but I really do not know how let Source = Excel. CurrentWorkbook(), #"Filtered Rows" = Table. SelectRows(Source, each ([Name] = "RegnsNotRenewedThisYear")) #"ExpandedContent" #"SortedRows" #"KeptLastRows" #"RemovedColums" I tried inserting "tbl=>" before the let and the "RegnsNotRenewedThisYear" to "tbl" but received an error. Thank you. |
#19
|
||||
|
||||
Perhaps some self teaching is required; I can't do it myself but here are some links you can look through:
Creating a Custom Function in Power Query / Power BI — Powered Solutions Creating a Custom Function in Power Query / Power BI — Powered Solutions and its follow up article: Understanding the Combe Files experience Power BI / Power Query — Powered Solutions Understanding the Combe Files experience Power BI / Power Query — Powered Solutions Create Custom Functions in Power Query – SQLServerCentral Create Custom Functions in Power Query – SQLServerCentral Power Query - Custom Functions - Excel Off The Grid Power Query - Custom Functions - Excel Off The Grid Create a Basic Custom Function in Power Query - YouTube Create a Basic Custom Function in Power Query - YouTube |
#20
|
||||
|
||||
Thank you for the links. I'm not giving up on this. Meantime, I have to make do with a quite circuitous process but with the same wanted results.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Highlight Duplicate Values Across Multiple Sheets | Bo_Knows_04 | Excel | 1 | 07-11-2020 11:41 PM |
VBA to highlight specific values in the spreadsheet | daiwuliz | Excel | 6 | 05-23-2018 10:30 AM |
Getting pop-up saying that my copy of Office is not registered. | dzuk | Office | 0 | 05-28-2015 05:09 PM |
find - reading highlight - highlight all / highlight doesn't stick when saved | bobk544 | Word | 3 | 04-15-2009 03:31 PM |
Interface not registered | dezwakke2 | Outlook | 0 | 12-03-2006 12:27 AM |