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
Qasim_Jan's avatar
Qasim_Jan
Frequent Visitor
2 years ago

Thanks AlienSx, yes I have seen that video but I was having hard time to digest that code.

AlienSx's avatar
AlienSx
Super 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
      Super 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.