Forum Discussion

pelowski's avatar
pelowski
Icon for Helper III rankHelper III
6 years ago
Solved

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 ...
Need Help
  • danextian's avatar
    danextian
    6 years ago

    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"
danextian's avatar
danextian
Icon for Super User rankSuper User
6 years ago

Hi pelowski ,

 

Try this:

let
//store [Date] in a variable
date = [Date]
in List.Transform ( Cultures, each Date.DayOfWeekName ( date, _ ) )
pelowski's avatar
pelowski
Icon for Helper III rankHelper III
6 years ago

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.

  • pelowski's avatar
    pelowski
    Icon for Helper III rankHelper III
    6 years ago

    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.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      pelowski
      Icon for Helper III rankHelper III
      6 years ago

      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"

       

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      pelowski
      Icon for Helper III rankHelper III
      6 years ago

      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's avatar
        pelowski
        Icon for Helper III rankHelper III
        6 years ago

        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.