table.ExpanTableColumn with changing columns' names

[fsim]

hi there,

Every month I get a brunch of 20 xls files containing 12+ columns. The name of those columns changed every month (typically previous 5 months, current month, next 6 months ) . How can I use the Table.ExpandTableColumn function without knowing the names of the columns ?

I tried Table.ExpanTableColumn(#"PreviousStep", "XlsContent",{*}) and, of course, it didn't work 🙂

I guess I should get them from a function, but I can't figure out wich one.

Can you help ?

thanks !

[Jimmy801]

Hello @fsim 

use this codesnippet to understand how to build your Table.ExpandColumn dynamically.

    ExpandAllColumns = Table.ExpandTableColumn
    (
        PreviousStep, 
        "ColumnToBeExpanded", 
        List.Union
        (
            List.Transform
            (
                List.Buffer(PreviousStep[ColumnToBeExpanded]), each Table.ColumnNames
                (
                    _
                )
            )
        )
    )
in
    ExpandAllColumns

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[fsim]

@Jimmy801  Hello ! Thank you for your answer. I will also check your solution ! 

kr,

Fred

[Jimmy801]

Hello @fsim 

that would be nice 🙂

Thank you

have a nice day

Jimmy

[Mariusz · Accepted solution]

Hi @fsim 

Please, see the attached file with a solution, it will look at all the column names within the tables and use this as a list to expand columns

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[fsim]

Hi @Mariusz  this is perfect, 

Thank you so much.