Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 09-26-2020, 05:30 PM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #17  
Old 09-26-2020, 06:00 PM
p45cal's Avatar
p45cal p45cal is offline Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #18  
Old 09-27-2020, 03:11 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #19  
Old 09-27-2020, 04:36 AM
p45cal's Avatar
p45cal p45cal is offline Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
but I really do not know how
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
Reply With Quote
  #20  
Old 09-27-2020, 05:58 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft