Combine excel files with different columns structure in Power Query

[domi25]

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? 

[slorin]

remove "Attribute" column after pivot, not before

[slorin]

Hi,

another solution : Unpivot + Pivot

let
  Source = Your_Source,
  Unpivot = Table.UnpivotOtherColumns(Source, {"Source", "Type"}, "Attribute", "Value"),
  Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Type]), "Type", "Value")
in
Pivot

Stéphane

[Qasim_Jan]

Hi @slorin

I select "Source" and" Type" column and Unpivot other columns which results in "Attribute" and "Value" column. Now when I select "Type" column--> Transform-->Pivot Column-->selelct "Value"-->"Don't Aggregate-->OK, it works. But if I remove "Attribute" column and then try Pivot, it result in error. When I peek into the error, it says something like this " Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
[List] "
Can you please elaborate on this error.
Thanks

[AlienSx]

Hi, @domi25 

let
    Source = your_table,
    f = (tbl) =>
        [a = Table.RemoveColumns(tbl, "Source"),
        b = Table.Transpose(a),
        c = Table.PromoteHeaders(b)][c],
    g = Table.Group(Source, {"Source"}, {{"tp", f}}),
    expand = Table.ExpandTableColumn(g, "tp", {"Revenue", "Category"})
in
    expand

[Qasim_Jan]

Hi@AlienX

Is this doable in PQ UI. If not can you explain the code, please.

Thanks

[AlienSx]

@Qasim_Jan How to integrate M-code into your existing solution 

[Qasim_Jan]

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

[AlienSx]

@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]

@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]

@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.