Syntax for Looping through a List in an M function

[pelowski]

I'm getting better at using List.Accumulate, am learning when and where to use List.Generate, but the syntax to the following eludes me.  Here is a basic example of what I'm trying to do.

1. I have a list of data
2. I want to perform a series of steps based upon each item in the list
3. I want to apply each one of those to table I provided the custom function

In the following example, let's say I want to return all the culture-specific day names from the list provided.  What syntax could I use in my custom function to accomplish this?  Any help you can provide will be greatly appreciated!

let
	Dates = List.Dates(#date(2018,1,1), 7, #duration(1,0,0,0)),
	#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
	Cultures = {"en-US", "de-DE", "fr-FR", "pl-PL", "ru-RU", "es-ES", "zh-HK"},

	fnAddCultures = (table as table, Cultures as list, fnc as function, name as text) =>
	let
		NewColumn = Table.AddColumn(table, Cultures{0} & " " & name, fnc)
	in
		NewColumn,

	AddColumn = fnAddCultures(#"Converted to Table", Cultures, each Date.DayOfWeekName([Date], List.First(Cultures)), "Weekday")
in
	AddColumn

[danextian]

Hi @pelowski ,

Try this:

let
//store [Date] in a variable
date = [Date]
in List.Transform ( Cultures, each Date.DayOfWeekName ( date, _ ) )

[pelowski]

@danextian, I don't understand what you're suggesting.  The problem becomes the same but from a different list's point of view.  I understand how to do a List.Transform, but I don't understand how to execute a looping construct over a list for a custom function I've written.  With the code you provided, now looping through the Dates list becomes the task but the problem is the same for me.  How do I execute a looping construct over the List?

What I'm trying to achieve in an example like this is the following...

I'm trying to follow Ben Gribaudo's example here, but I wish I could see how the function he wrote gets executed to better understand how the looping works.

[danextian · Accepted solution]

Hi @pelowski ,

My understanding was you just wanted to loop through each item in a list but not create tables out of it. The screenshot would have helped 😊. Anyway, the custom function below creates a table of name of day based on a list of cultures. I didn't use List.Accumulate as  this could be slow if used on a large dataset.

let fnAddCultures = ( date as date, Cultures as list ) as table=>
	let
		list =  List.Transform ( Cultures, each Date.DayOfWeekName ( date, _ ) ),
		table =  Table.FromList(list, null),
		transpose =  Table.Transpose(table),
		newname = List.Zip({Table.ColumnNames(transpose), Cultures})
	in
		Table.RenameColumns(transpose, newname)
in fnAddCultures

 And here's a sample table generated from a list of dates and the custom function

let
    Dates = Table.FromList(List.Dates(#date(2010, 1, 1 ), 365.25 * 25, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(Dates, "Day Name in Culture", each fnAddCultures([Dates], Cultures[Column1])),
    #"Expanded Day Name in Culture" = Table.ExpandTableColumn(#"Invoked Custom Function", "Day Name in Culture", {"en-US", "de-DE", "fr-FR", "pl-PL", "ru-RU", "es-ES", "zh-HK"}, {"en-US", "de-DE", "fr-FR", "pl-PL", "ru-RU", "es-ES", "zh-HK"})
in
    #"Expanded Day Name in Culture"

[pelowski]

@danextian, thanks for the additional response and sorry for the confusion!  I tried to include what I wanted in my original message but yes, the screenshot would've helped.

The big thing I needed to understand is how to loop through a list (or table column as you've done here) and iterate over another data source (table in this case) and your example helps me greatly.  Thank you!

For anyone reading this, I think Dan's second query needed one additional "Cultures" table with a Column1 defined for it all to work together.

let
    Dates = Table.FromList(List.Dates(#date(2010, 1, 1 ), 365.25 * 25, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
    Cultures = #table({"Column1"}, {{"en-US"}, {"de-DE"}, {"fr-FR"}, {"pl-PL"}, {"ru-RU"}, {"es-ES"}, {"zh-HK"}}),
    #"Invoked Custom Function" = Table.AddColumn(Dates, "Day Name in Culture", each fnAddCultures([Dates], Cultures[Column1])),
    #"Expanded Day Name in Culture" = Table.ExpandTableColumn(#"Invoked Custom Function", "Day Name in Culture", {"en-US", "de-DE", "fr-FR", "pl-PL", "ru-RU", "es-ES", "zh-HK"}, {"en-US", "de-DE", "fr-FR", "pl-PL", "ru-RU", "es-ES", "zh-HK"})
in
    #"Expanded Day Name in Culture"

[pelowski]

To put this in a slightly different way, I'm trying to loop over one object in the context of another.  In this case, I'm trying to loop through what amounts to two lists in the context of a table in order to add a column for each item in a list and a row for each value in another list.  I've done plenty of for/each and do/until loops in other languages but the syntax of doing this in Power Query is tripping me up.

[Jimmy801]

Hello @pelowski 

could you please explain what is your exact goal for the table shown?

What was mentioned by @danextian  is just right and the way how a list can be used.

You can use List.Transform to invoke a custom function and use every list-entry as a parameter for you custom function.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[pelowski]

@Jimmy801 @danextian, how/where do I plug in what @danextian suggested to get from my initial code to the end result?  I tried adding his code, but wasn't sure where and how the date variable inherited the current date in the list.  If I can see it all put together I'm sure I can reuse this syntax is many different areas, but obviously I'm at a loss at how to put it all together.  Sorry!

[pelowski]

I figured it out using this response that you provided @Jimmy801 

let
	Dates = List.Dates(#date(2018,1,1), 7, #duration(1,0,0,0)),
	DatesAsTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
	Cultures = {"en-US", "de-DE", "fr-FR", "pl-PL", "ru-RU", "es-ES", "zh-HK"},
    LoopThroughWithListAccumulate = List.Accumulate({0..List.Count(Cultures)-1}, DatesAsTable, (old, current) => Table.AddColumn(old, Cultures{current} & " Weekday", each Date.DayOfWeekName([Date], Cultures{current}), type text))
in
    LoopThroughWithListAccumulate

Thanks for your help from both of you.  I didn't realize that a table manipulation function could be the inner function for a List.Accumulate.

[Jimmy801]

Hello @pelowski 

so everything fine then?

Jimmy

[pelowski]

Yes, thank you!  I would appreciate understanding how the code @danextian could or would fit into an overall solution, (which I still don't understand) but that's just a nice-to-have; now I see how a List.Accumulate function can be used in combination with Table functions to achieve the looping I was trying to accomplish.  Thanks again for your help!

[Jimmy801]

Hello

So i would appreciate to mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy