Forum Discussion

nightfall's avatar
nightfall
Icon for Helper I rankHelper I
5 years ago
Solved

Append 4 columns of the same table into 2

Hello everybody!

I have the following table with 4 columns:

Costs | CValues | Revenues | RValues

cost1 | 100        | revenue1 | 40

cost2 | 50          | revenue2 |14
cost3.....

and i am trying to append costs and revenues and the respective values in 2 columns:
Costs + Revenues | CValues +Rvalues
cost1                     | 100
cost2                     |50
cost3....                  |....
revenue1               | 40
revenue2               | 14
revenue3.....           | ....

Since it is supposed to be the "Transform sample file", as far as i understand i cannot copy/duplicate the table and then append them...
Do you have any hints?

Thank you and best regards,

 

Vittorio

Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    i.e. Your last step should be

     

     

    #"new table" = Table.FromColumns({#"Removed Columns"[Column1]&#"Removed Columns"[Column3], #"Removed Columns"[Column2]&#"Removed Columns"[Column4]}, {"Conto","Importo"})

     

5 Replies

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    5 years ago

    I hope this can help...

     

    NewTable = Table.FromColumns({Source[Costs]&Source[Revenues], Source[CValues]&Source[RValues]}, {"Costs + Revenues","CValues + Rvalues"})

     

    If you apply it in transformation process, you can sort Costs + Revenues column upon invoking Transform Function. 

    • nightfall's avatar
      nightfall
      Icon for Helper I rankHelper I
      5 years ago

      Hello Jakinta thank you for your prompt reply.

      it's giving me the following error:
      Expression.Error: We cannot convert a value of type Table to type List.
      Details:
      Value=[Table]
      Type=[Type]

       

      the code is this:

      let
      Source = Excel.Workbook(Parameter1, null, true),
      #"Doc 1" = Source{[Name="Doc 1"]}[Data],
      #"Changed Type" = Table.TransformColumnTypes(#"Doc 1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}}),
      #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column5", "Column6"}),
      #"new table" = Table.FromColumns(#"Removed Columns",{{Source[Column1]&Source[Column3], Source[Column2]&Source[Column4]}, {"Conto","Importo"}})
      in
      #"new table"

       

      I will investigate on this.

       

      Thank you again in the meantime.

       

      Vittorio

      • Jakinta's avatar
        Jakinta
        Icon for Solution Sage rankSolution Sage
        5 years ago

        If you replace Source in my line, with whichever step/table is the same structure as in your initial post, it should work.