Forum Discussion
Syntax for Looping through a List in an M function
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"
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.
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"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"