cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Expand all columns using loop ExpandTableColumn with a loop

Hello Power Query 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)

colone non dev.png

 

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. 

begin.png

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.colone a dev.png

 

 

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. 

variable.png

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

7 REPLIES 7
Community Support
Community Support

Hi @Remy320 ,

 

Why do you want to get something like below?

Index W46 - A W46 - B W46 - C W45 - A W45 - B W45 - C
1 A1 B1 4 A1 B1 1
2 A2 B2 5 A2 B2 2
3 A3 B3 6 A3 B3 3

 

It is not suggested to transform data like so. Power BI, or DAX, is calculated in units of columns. So, transform data like below is better:

Name Index A B C
W45 1 A1 B1 1
W45 2 A2 B2 2
W45 3 A3 B3 3
W46 1 A1 B1 4
W46 2 A2 B2 5
W46 3 A3 B3 6
W47 1 A1 B1 7
W47 2 A2 B2 8
W47 3 A3 B3 9

 

Then, you can filter what you want.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III
Super User III

Hello @Remy320 

 

You need to combine this data in one table, or you need the data of your worksheets in separate columns?

In you first scenario you could read from your Excel-file (without accessing a sheet), filter your sheets as needed, transform the column "data" to promote headers (not needed if you have tables instead of sheets) and then to use a Table.Combine on your "data"-column. Here an example how it could look like

let
    Source = Excel.Workbook(File.Contents("xxxx"), null, true),
    Transform = Table.TransformColumns
    (
        Source,
        {
            {
                "Data",
                each Table.PromoteHeaders(_)
            }
        }
    ),
    Combine = Table.Combine(Transform[Data])
in
    Combine

Jimmy801_0-1604039106813.png

 

in your second scenario you could add a new column, where you make the transformation, to rename the columns

let
    Source = Excel.Workbook(File.Contents("xxx"), null, true),
    Transform = Table.TransformColumns
    (
        Source,
        {
            {
                "Data",
                each Table.PromoteHeaders(_)
            }
        }
    ),
    TableAdd= Table.AddColumn
    (
        Transform,
        "newData",
        each Table.RenameColumns(_[Data], List.Zip({Table.ColumnNames(_[Data]), List.Transform(Table.ColumnNames(_[Data]), (item)=> _[Name]&" - "&item )}))
    ),
    Combine = Table.Combine(TableAdd[newData])
in
    Combine

 

Jimmy801_1-1604039161379.png

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

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

 

Hello Jimmy801, Thanks for taking times to help me.  I need to combine them to only one table with 43 rows (Not more) 

Remy320_0-1604299138917.png

Like here (I develloped a table, and i need devellop the 80 columns and keep having only 43 rows and +999 columns).this is the target i expect.

 

However with your 1st solution i get +999 rows

Remy320_1-1604299389103.png

And 2nd solutions get me way too much rows (+999) and columns. 

 

Remy320_2-1604299484980.png

Do you have any other ideas how can i do that ? 

Thank you for the support. 

 

Best regards

Rémy

 

 

 

Hello @Remy320 

 

your request was to combine all sheets in your file in one table. If you get more then 1000 rows means, that you have more then 1000 rows in all your sheets. I don't know how to get to 43 only!?

 

BR

 

Jimmy

Hi, @Jimmy801 

 

i don't have any table with 1000 rows, they all have the same 43 rows. 

 

All the table have the same rows (43) and between 40 and 50 columns. (Each worksheet have the same rows, and every week a new worksheet is made)

So the thing is i don't want to append datas, i want to make the same result as if i expand each table (For example if i have 100 tables, a single table with 43 rows and 100*50 columns)

 

Best regards 

Rémy

Hello @Remy320 

 

so your final goal is to have in power bi 100 different tables or these 100 tables not appended but connected horizontal?

 

BR

 

Jimmy

My final goal is just to connect these table horizontal (just like a append would do it vertical but i need horizontal)

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors