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

10 Replies

  • 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

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

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

    remove "Attribute" column after pivot, not before