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
Most probably, you get the last loop value, because you are not accumulating the results.
Here is an example of a recursive function:
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..
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
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.
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.
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).
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?!
