Forum Discussion

pect's avatar
pect
Helper I
8 years ago
Solved

Table transformation

Hello,   I am trying to transform a table which is: Client Estat Any Import EU Línea base 2017 10000 EU Adjudicat 2017 3000 EU En contractació 2017 2000 EU En contracta...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    The suggested (un)pivot solution could look like this:

     

    let
    Source = Table1,
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Estat]), "Estat", "Import"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Pendent", each [Línea base] - [Adjudicat] - [En contractació], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Línea base"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Client", "Any", "Pendent", "Adjudicat", "En contractació"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Client", "Any"}, "Attribute", "Value")
in
    #"Unpivoted Columns"
Salfii's avatar
Salfii
Advocate IV
8 years ago

Hi pect,

 

go to the query Editor:

 

and add a "Custom Column"

 

 

Than you can craete a new, custom column with the formular you stated:

 

 

pect's avatar
pect
Helper I
8 years ago

Hi,

 

Thanks but I am afraid this doesn't work with my request. My calculation depends on only one column "Import".

 

I appreciate any other help you could provide me!

 

Thank you very much.