How to Expand all columns using loop ExpandTableColumn with a loop

[Remy320]

Hello Power Bi expert,  🙂

this is my Excel table i would like to analyse with power bi(but first to transform with power query). I would like to expand all table at once time(Every week a new table will be added, so next week a Week45_2020 will be created) 

Here is the code related to the table, i charge a Onedrive file which is supplied by a power automate connected will my server exchange to download files from my emails. then remove some columns and remains the date and rows with datas i need to analyse. 

I can do it manually but it's really annoying and not very useful if i need to go back to my file every week to expand all.

The Table Function "Table.expandTableColumn" take as argument the main table(here is headers from the previous step), the column name and the new name. 

Then i would like to make this week44_2020 dynamic, like a loop which can go through a list of my first column which contain all the worksheet name. For now it's a list and the function is only accepting column text value so i didn't make it to expand all this thing the dynamic way. Or maybe with a each function, i'm not that familiar with this function. 

 I'm just an manufacturing engineer without so much software background and new in Power Bi and m language area so i'm not so familiar how to do this. That's why i ask help here. 

Could anyone advise on  how to solve this problem ?

I already look for it for 4days without any results. I also checked these website and tried to adapt their code without success.

https://community.powerbi.com/t5/Desktop/Expanding-Multiple-Data-Tables-with-Unknown-Column-Names/m-...

https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/

https://bielite.com/blog/dynamically-expand-all-columns/

(The second one is making a giant loop of many gigabite until my computer crash)

Thank you very much in advance.

Best regards 

Rémy

[lbendlin]

Have you considered using Python script inside Power Query?

[Remy320]

Let me explain simply :

I have many tables, the first columns of each table is the same and has 43 rows. all the rest change.

So i want to let only the first 43rows and connect everything horizontally (In python it's super easy with the concat command https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html

where you can choose all excel file and concat horizontally all the sheets. I don't understand why it's so complicated in Power query.

Best regards

Rémy

[v-easonf-msft]

Hi, @Remy320 

The second way your mentioned may be affected by performance.

Please check if the solutions in these threads  can work for you.

https://community.powerbi.com/t5/Desktop/Expand-multiple-columns-in-power-query/td-p/695129

https://community.powerbi.com/t5/Desktop/Expanding-Multiple-Data-Tables-with-Unknown-Column-Names/td... 

Best Regards,
Community Support Team _ Eason

[lbendlin]

You will want to seriously rethink your data sourcing process.

What was the rationale for the initial Transpose command?

Please provide sample data and indicate the expected result.