Forum Discussion
M-Code looping
Im struggling to get a grip of Power Query. I've been trying to run all the tables (tables with the same structure btw) from sheet1 through one Power Query-question. I suspect it might be possible with some kind of m-code loop function magic.
My previous solution was to duplicate the power query script and change the input from "table1" to "table2" and so forth. I then figured it might be better of doing it the right way, since i ran int a bunch of problems.
I would so much appreciate any clues. Thank you in advance!
6 Replies
808 You likely want to write a function in Power Query that you can call for each of your tables. There is no real "looping" in Power Query. There is recursion.
The List.Generate function is very similar to classic looping.
That said, I try to avoid it if at all possible in favor of leveraging the functional nature of M. For example, if you have a list of tables you want to do the same transformation on, you'd write something like List.Transform(ListOfTables, TransformationFunction) rather than iterating through the list like a loop.
Here's an example (that I just finished writing) that transposes and promotes headers for a list of tables:
Hey there Alexis! I really tried to wrap my head around your suggestion, but I think I might have been vague describing my issue. In that case i'm sorry. Please let me try to explain with some context and screenshots.
Workbook1, Sheet1
Power Query flowchart
1. In Workbook1, Sheet1, I have a set of tables with the same format, and I want to run them all through the "PQ 1" power query question as shown in the flowchart.
Curated data when pasted in Workbook2
2. After that, I need to pass the data through three separate PQ-questions. The output from "PQ 2a" as shown above is to be pasted as ranges one after another into a separate workbook (Workbook2).
Curated data when pasted in Workbook3
Curated data when pasted in Workbook4
3. Furthermore, after passing through "PQ 2b", the data should be pasted into separate workbooks. Workbook3, 4, 5, and 6 since there were four tables this time. Examples of Workbook3&4 shown above).
4. Finally, after passing through "PQ 2c", I need the data consolidated into a single table and pasted into Workbook2 aswell.I realise that doing this task requires some VBA coding along with dealing with the Power Query-hassel. Regarding Power Query, based on what I've mentioned, do you still think that using the List.Transform function is the best way to go? Or do you have any other suggestions?
I have some really basic knowledge of VBA coding, so if you have any suggestions or tips for the VBA part, I'd really appreciate it aswell. Thank you so much!
Hey Greg! I really appreciate you taking the time to clarify that for me. Thank you!
