• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Create Nested Custom Functions|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Create Nested Custom Functions|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCreate Nested Custom Functions
sp_PrintTopic sp_TopicIcon
Create Nested Custom Functions
Avatar
Peter Fresh
Member
Members
Level 0
Forum Posts: 13
Member Since:
August 6, 2020
sp_UserOfflineSmall Offline
1
November 26, 2021 - 8:30 pm
sp_Permalink sp_Print

Hello all,

I am trying to get things done with a looping custom function in PQ.

Here is my scenario:

Goal: 1 table (flight schedule) with information about calendar week splitted into separated tables for each calendar week.

Example of the given columns in the flight schedule:

calendar_week, date, airline, trip_number, prev_or_nextairport, bound, scheduled_time

My approach:

One custom function for filtering the flight schedule by calendar week. <- this is working by this code

(datatable as table, calendar_week as number) =>
let
output = Table.SelectRows(datatable, each ([calendar_week] = calendar_week))
in
output

Now, the tricky part:

I wanted to create another custom function which iterates through all different calendar weeks in the flight scheduled and create one table for each week. (Hopefully, you get the point of my attempt)

This is what I have so far:

(totalLoops as number, Loop as number, Value as number) =>
let
CurrentLoop = Loop + 1,
CurrentValue = Value + 1,
output =
if CurrentLoop >= totalLoops
then fx_FilterByWeek(tbl_flight_schedule, CurrentValue)
else @fx_looping(totalLoops, CurrentLoop, CurrentValue)
in
output

Unfortunately, I recieve only one table with one week depending on what numbers of loops I will use as an input.

Are there any ideas to fix this problem? Thanks in advance for spending your time.

Best regards

Peter

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
November 26, 2021 - 8:56 pm
sp_Permalink sp_Print

Most probably, you get the last loop value, because you are not accumulating the results.

Here is an example of a recursive function:

https://www.myonlinetraininghu.....y-1#p20535

Avatar
Peter Fresh
Member
Members
Level 0
Forum Posts: 13
Member Since:
August 6, 2020
sp_UserOfflineSmall Offline
3
November 26, 2021 - 10:59 pm
sp_Permalink sp_Print

Thanks for this quick resond. Unfortunately I am not getting the wanted outcome.

I added following text to the function code in order to accumulate the results: fx_FilterByWeek(tbl_flight_schedule, CurrentValue)

(totalLoops as number, Loop as number, Value as number) =>
let
CurrentLoop = Loop + 1,
CurrentValue = Value + 1,
output =
if CurrentLoop >= totalLoops
then fx_FilterByWeek(tbl_flight_schedule, CurrentValue)
else fx_FilterByWeek(tbl_flight_schedule, CurrentValue) & @fx_looping(totalLoops, CurrentLoop, CurrentValue)
in
output

But, my goal is it to call fx_FilterByWeek as often as needed with following outcome:

Invoked Function 1 (for example flight schedule of calendar week 45) => a separated table listed in the query overview window

Invoked Function 2 (for example flight schedule of calendar week 46) => a separated table listed in the query overview window

etc.. 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
November 26, 2021 - 11:36 pm
sp_Permalink sp_Print sp_EditHistory

I added following text to the function code in order to accumulate the results: fx_FilterByWeek(tbl_flight_schedule, CurrentValue)

Sorry, not seeing where you acccumulate the loop results.

If the invoked function output is a table, to accumulate you should use Table.Combine:

should look like:

output=Table.Combine({ fx_FilterByWeek(tbl_flight_schedule, CurrentValue) , @fx_looping(totalLoops, CurrentLoop, CurrentValue)})

If the function returns a json format, then to accumulate results it just needs to use the & char, like in the example:
ResponseData & GetNextPageData(api_url,path,PageNumber+1,TotalPages, token)

In red is the current response, in blue is the next loop call.

Always apply accumulation expression based on the returned data type, if it returns a list then use List.Combine

Avatar
Peter Fresh
Member
Members
Level 0
Forum Posts: 13
Member Since:
August 6, 2020
sp_UserOfflineSmall Offline
5
November 29, 2021 - 11:46 pm
sp_Permalink sp_Print

I am sorry, but I don't understand that code at all.

My current code looks like this:

(totalLoops as number, Loop as number, Value as number) =>
let
CurrentLoop = Loop + 1,
CurrentValue = Value,
output =
if CurrentLoop >= totalLoops
then fx_FilterByWeek(tbl_Monatsflugplan, CurrentValue)
else Table.Combine({ fx_FilterByWeek(tbl_Monatsflugplan, CurrentValue) , @fx_looping(totalLoops, CurrentLoop, CurrentValue+1)})
in
output

With an orginial table containing the flights of the weeks 27-31, when I call the function fx_looping(5,0,27). I just get the same result as my original table <- this doesn't make sense to me.

At the end, I need to have 5 different tables. Not one table including 5 weeks, because that would be just the same table as the original fact table.

My idea was something like, calling a function which filters the flight schedule by week and creates one output. Because the flight schedule contains several weeks, I would call the function as many times as different weeks are in the schedule itself.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
November 29, 2021 - 11:51 pm
sp_Permalink sp_Print sp_EditHistory

At the end, I need to have 5 different tables. Not one table including 5 weeks, because that would be just the same table as the original fact table.

Not following. A query will produce 1 table, not multiple tables, no matter how many iterations you have in the loop.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
November 29, 2021 - 11:59 pm
sp_Permalink sp_Print

I think the idea for  this scenario is  wrong, you should  not split data. Anyway, power query does not split data, the  main purpose of PQ is bringing data together and cleaning data.

Why not create a  pivot table from your table, using slicers to see data by week?
You can use Show Report Pages  option, that SPLITS data (if the filter section has the week number).

Avatar
Peter Fresh
Member
Members
Level 0
Forum Posts: 13
Member Since:
August 6, 2020
sp_UserOfflineSmall Offline
8
November 30, 2021 - 12:20 am
sp_Permalink sp_Print

I was wondering if PQ is able to create 5 different tables by just calling a master function which triggers sub functions. The sub functions are producing its own output. Instead of combing the outputs I like to keep these in a separated way.

In VBA I would filter that table, copy the filtered output and paste it into a new worksheet. I would use a do while loop for this in order to seperate all weeks from that flight schedule. However, VBA is no solution for me, because I need to handover the file to others who don't have access to VBA / macros. Therefore, I try to come up with a solution which uses PQ. 

(I am aware that VBA is an object oriented language and loops are much easier to implement.)

You can use Show Report Pages  option, that SPLITS data (if the filter section has the week number).

I will give this a try - will this option be dynamically as well?!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
November 30, 2021 - 10:14 am
sp_Permalink sp_Print

Hi Peter,

Here is a tutorial on using PivotTables to show report pages as Catalin suggests.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.