Forum Discussion
Syntax for Looping through a List in an M function
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.
- I have a list of data
- I want to perform a series of steps based upon each item in the list
- 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
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 fnAddCulturesAnd 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"
11 Replies
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.
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.
