M-Code looping

[808]

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!

[Greg_Deckler]

@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.

[808]

Hey Greg! I really appreciate you taking the time to clarify that for me. Thank you!

[AlexisOlson]

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:

https://community.powerbi.com/t5/Power-Query/Transforming-data-with-repeated-date-headers/m-p/309681...

[808]

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!

[AlexisOlson]

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").

[808]

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.