Forum Discussion

domi25's avatar
domi25
Frequent Visitor
2 years ago

Combine excel files with different columns structure in Power Query

Hi,  I want to combine excel files in Power Query. I need to pull from each file two rows Revenue and Category. Each file has different categories. Is it possible to tranform the table in this way? ...
Need Help
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

Qasim_Jan Table.Group groups rows of original table by values in Source (to new column "tp") and applies function f to each group of rows. This function itself is simple: removes Source column, transposes (so that Type becomes the first row) and promotes Type to header. Then we expand this   

Qasim_Jan's avatar
Qasim_Jan
Frequent Visitor
2 years ago

AlienSx Can't we apply those three operation: Remove Column, Transpose, Promote Header on the main table "Source" instead of defining function for it? Please go easy on me as I am starting in PQ.

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    Qasim_Jan no. You can check that yourself and see that this won't work. This set of transformations must be applied to each group of rows. That's why we group them first.