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
slorin's avatar
slorin
Icon for Super User rankSuper User
2 years ago

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

    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