Forum Discussion

pect's avatar
pect
Icon for Helper I rankHelper 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"
pect's avatar
pect
Icon for Helper I rankHelper 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.

Salfii's avatar
Salfii
Icon for Advocate IV rankAdvocate IV
8 years ago

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's avatar
    Salfii
    Icon for Advocate IV rankAdvocate IV
    8 years ago

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

     

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

     

     

     

  • pect's avatar
    pect
    Icon for Helper I rankHelper I
    8 years ago

    How do I join the 3 tables?

    • Salfii's avatar
      Salfii
      Icon for Advocate IV rankAdvocate IV
      8 years ago

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

       

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

       

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        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"