cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pect Regular Visitor
Regular Visitor

Table transformation

Hello,

 

I am trying to transform a table which is:

ClientEstatAnyImport
EULínea base201710000
EUAdjudicat20173000
EUEn contractació20172000
EUEn contractació20185000
EUAdjudicat201810000
EULínea base201820000

 

And I would like to transform it, so that I had a new "Estat" called "Pendent" with the calculation "Pendent" = "Línea base" - "Adjudicat" - "En contractació". The resulting table would be:

 

ClientEstatAnyImport
EUPendent20175000
EUAdjudicat20173000
EUEn contractació20172000
EUEn contractació20185000
EUAdjudicat201810000
EUPendent20185000

 

This transformation would enable me to represent a stacked column chart I am looking for.

 

Could anyone shed a light please?

 

Thank you in advance!

 

Àlex

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Table transformation

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"
Specializing in Power Query Formula Language (M)
9 REPLIES 9
Salfii Regular Visitor
Regular Visitor

Re: Table transformation

Hi @pect,

 

go to the query Editor:

 

grafik.png

and add a "Custom Column"

 

grafik.png

 

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

 

grafik.png

 

pect Regular Visitor
Regular Visitor

Re: Table transformation

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.

Salfii Regular Visitor
Regular Visitor

Re: Table transformation

Now I understand!

 

  1. Right Click on the table in query Editor and Click "Rerence". Do this 3 times and call every tables coresponding to the "Estat" Values "Línea base" ,"Adjudicat", "En contractació" for example.
  2. Filter every table to only display the corresponding Estat value "Línea base" ,"Adjudicat", "En contractació". (Click on the Arrow in the column header and filter it) and call the coumns "Línea base" - "Adjudicat" - "En contractació".
  3. Now you can join the tables based on the  columns "client" and "any" or any other kind of identification column(s) in one new table.
  4. Now you have 3 columns next to each other with the values of "Línea base" ,"Adjudicat", "En contractació" and you can use a custom column.
  5. Dont forgeht to disable "Enable Load" on the intermediate querys which you dont need in your final data model and RAM (Right click on querys and Disable"Enable Load").

Does this help you`?

 

 

Salfii Regular Visitor
Regular Visitor

Re: Table transformation

Alternative: (you have to try this, i´m not exactly sure)

 

fondle around with "unpivot column" or "pivot column"

grafik.png

 

 

 

pect Regular Visitor
Regular Visitor

Re: Table transformation

How do I join the 3 tables?

Salfii Regular Visitor
Regular Visitor

Re: Table transformation

Joining was the wrong word, in Power BI its called "Appending".

 

Click on the black arrow next to it and use "append as new"

 

grafik.png

Super User
Super User

Re: Table transformation

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"
Specializing in Power Query Formula Language (M)
pect Regular Visitor
Regular Visitor

Re: Table transformation

Thank you very much! It works!

Community Support Team
Community Support Team

Re: Table transformation

Hi @pect,

 

Could you please mark the proper answer as solution? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.