List Generate first step

Power Query M Code Showcase
1

Hi @Sam,
I’m having a hard time understanding List.Generate.

I’m generating a set of rows in a table with the
date and fiscal period.
All seems to be ok but the second row is a duplication of the first.

Where I’m doing wrong?

Thanks

Roberto

List.Generate class.pbix (18.0 KB)

let
Source = List.Generate(
() => [
index = 0,
date = #date(2023, 4, 1),
Period = GetFiscalPeriod(date, 4)
],
each [index] < 70,
each [
index = [index] + 1,
date = Date.AddQuarters([date], 1),
Period = GetFiscalPeriod([date], 4)

	],
	each Record.FromList(
		{[date],  [Period]},
		{"Data", "Fiscal Period"}
	)
	),
TableFromList = Table.FromList(
	Source,
	Splitter.SplitByNothing(),
	{"Dates"},
	null,
	ExtraValues.Error
),
ListOfColumns = Record.FieldNames(
	TableFromList[Dates]{0}
),
ExpandedRecords = Table.ExpandRecordColumn(
	TableFromList,
	"Dates",
	ListOfColumns,
	ListOfColumns
),
Types = {
	{"Data", type date},

{“Fiscal Period”, type text}

},
#"Changed Type" = Table.TransformColumnTypes(
	ExpandedRecords,
	Types
)

in
#“Changed Type”

2

@Roberto I don’t see any duplicate row?

3

Hi @AntrikshSharma ,
you’re right and I’m double wrong. I must have done alast-minute change to the code before asking for help.
This List.Generate function drives me crazy.
Debugging to understand how it works was possible after I watched a @BrianJ video, but still it’s one of the most complex and power functions I’ve found in M.
Thanks for your time

Regards

Roberto

1 Like
4

@Roberto ,

I’m glad you found that debug video helpful. That’s often how I get list.generate sorted out. It’s pretty much acknowledged to be the most difficult M function by Gil Raviv and others, but once it clicks it’s invaluable.

The resource that I found most helpful in understanding it was Rick de Groot’s blog entry:

He also did an excellent session at one of our summits last year entitled "The Magic of List.Generate
https://app.enterprisedna.co/app/virtual-events?id=224

  • Brian
1 Like
6

Hello @Roberto

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you