Forum Discussion
M-Code looping
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!
I'm having some trouble following. Can you link to a sample file, that is, upload an Excel file to Dropbox/Google Drive/OneDrive/SharePoint and share it via a publicly accessible link? Please include the expected result.
I think this should be possible without VBA but I'd like to tinker with an actual file to feel confident about that.
Language note: I think you mean "query" when you write "question". These are similar words but when the subject involves data tables and code that operates on it, the word "query" is the one that should be used (even though you get weird phrases like "Power Query query").
Thank you for the information. It's directly translated from Swedish. I also felt that it sounded a bit strange. Regarding Excel, great idea, I will definitely make one! I really appreciate this, you have no idea how valuable this is for me.