List.Generate through API

[ArjenPBI]

For my work I got the assigment to make a dashboard which includes the timesheets of all employees. We use third-party software to regist the hours we have worked for.

The software offers a REST API to get our data, however they only allow to pull 1 month of a specified year. (so after the api URL I need to include month=#&year=#

So I wanted to loop through each month which has data, and increase the year when the month count passes 12.
I've been using a video tutorial to help me since this is the first time I am actively working with m.

The tutorial thought me how to make a function where you just have to give a month&year num, which works fine. The tutorial next went into how to automatically get all data using List.Generate. At first I thought I understood it and made the code to get month 1-12 and add a year + set month back to 1 once it hits 12. However I only got 1 month back which in the list stated error.
The code:

= List.Generate(

    () => [maand = 1, 
          jaar = 2022, 
          data = FnMaandOphalen(1, 2022)],
		  
    each not List.IsEmpty([data]),
	
    each if [maand] <= 12 
         then [
		      maand = [maand] + 1, 
              data = FnMaandOphalen([maand], [jaar])
			  ]
         else [
		      maand = 1, 
              jaar = [jaar] + 1, 
              data = FnMaandOphalen([maand], [jaar])
			  ] 
)

results:

Inside the list is the correct data for month 1.

Next I decided to simplify the code since I figures I had an error in my if-statement, So I cut off the whole year increment and just went for month.
code:

= List.Generate(
    () => [maand = 1, 
          data = FnMaandOphalen(1)],
    each not List.IsEmpty([data]),
    each [maand = [maand] + 1, data = FnMaandOphalen( [maand] )]
)

But this resulted in the same error. 

Is there anyone that could tell me what I did wrong? for as far as I can see the second part of code is pretty much exactly the same as what I've seen in the tutorial.

PS. I'm not sure if I'm allowed to share the video link here to show which tutorial I used. however if you search for Power Query List.Generate on that website (which has a red logo with a white play button 😉 ) the video I watched is the second one.

[jbwtp · Accepted solution]

Hi @ArjenPBI,

I think this may be a simpler/more straightforward alternative:

let
    Years = Table.FromList({2022..2025}, Splitter.SplitByNothing(), {"jaar"}, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(Years, "maand", each {1..12}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "maand"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"jaar", Int64.Type}, {"maand", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Data", each FnMaandOphalen([maand], [jaar]))
in
    #"Added Custom1"

Cheers,

John

[ArjenPBI]

Thank you very much @jbwtp 
This works great!