Forum Discussion

bman6074's avatar
bman6074
Helper I
6 years ago
Solved

Transpose Table

Hi, Is there a way to transpose/pivot this (Current picture) table to (Transpose picture) So each name is across the top and the usage risk scores listed below? Then ultimatly I want to take highes...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi bman6074 ,

     

    We can transpose it by using "Transform" - "Povit Column" in Power Query Editor, then select maximum as aggreration function:

     

     

    Or we can append this step in your queries

    let
Source = Sql.Databases("database.power bi"),
Powerbi= Source{[Name="powerbi"]}[Data],
snow_AllRelationshipsKeyTable = powerbi{[Schema="snow",Item="AllRelationshipsKeyTable"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(snow_AllRelationshipsKeyTable,{{"EOL", type date}, {"EOSL", type date}, {"LCS_GA", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "Usage_Risk_Score"}),
PivotStep = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Changed Type"[Name]), "Name", "Usage_Risk_Score", List.Max)
in
PivotStep

     


    By the way, PBIX file as attached.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi bman6074 ,

     

    Please try to change the data type of Usage_Risk_Score column to whole number before unpivot,

     

     

    Or use the following query:

     

    let
Source = Sql.Databases("database.power bi"),
Powerbi= Source{[Name="powerbi"]}[Data],
snow_AllRelationshipsKeyTable = powerbi{[Schema="snow",Item="AllRelationshipsKeyTable"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(snow_AllRelationshipsKeyTable,{{"EOL", type date}, {"EOSL", type date}, {"LCS_GA", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "Usage_Risk_Score"}),
ChangeType = Table.TransformColumnTypes(#"Removed Other Columns",{{"Name", type text}, {"Usage_Risk_Score", Int64.Type}}),
PivotStep = Table.Pivot(#"ChangeType", List.Distinct(#"Changed Type"[Name]), "Name", "Usage_Risk_Score", List.Max)
in
PivotStep

     


    Best regards,

bman6074's avatar
bman6074
Helper I
6 years ago

Hi Ricardo,

thanks for replying.

How do i add it to the current code in the advance editor?

 

let
Source = Sql.Databases("database.power bi"),
Powerbi= Source{[Name="powerbi"]}[Data],
snow_AllRelationshipsKeyTable = powerbi{[Schema="snow",Item="AllRelationshipsKeyTable"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(snow_AllRelationshipsKeyTable,{{"EOL", type date}, {"EOSL", type date}, {"LCS_GA", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "Usage_Risk_Score"})
in
#"Removed Other Columns"

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi bman6074 ,

 

We can transpose it by using "Transform" - "Povit Column" in Power Query Editor, then select maximum as aggreration function:

 

 

Or we can append this step in your queries

let
Source = Sql.Databases("database.power bi"),
Powerbi= Source{[Name="powerbi"]}[Data],
snow_AllRelationshipsKeyTable = powerbi{[Schema="snow",Item="AllRelationshipsKeyTable"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(snow_AllRelationshipsKeyTable,{{"EOL", type date}, {"EOSL", type date}, {"LCS_GA", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "Usage_Risk_Score"}),
PivotStep = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Changed Type"[Name]), "Name", "Usage_Risk_Score", List.Max)
in
PivotStep

 


By the way, PBIX file as attached.


Best regards,

 

  • bman6074's avatar
    bman6074
    Helper I
    6 years ago

    Hi Community Support Team _ Dong Li

    Thanks for the reply. Unfortunatly that doesn't acheive what I need. Its not ending with highest value in the list of usage risk score. 

    For example the Advantex Gas modile has 4 scores and 2 are 10. Result should end with highest value which would be 10 but it ended up with 5

     
    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi bman6074 ,

       

      Please try to change the data type of Usage_Risk_Score column to whole number before unpivot,

       

       

      Or use the following query:

       

      let
Source = Sql.Databases("database.power bi"),
Powerbi= Source{[Name="powerbi"]}[Data],
snow_AllRelationshipsKeyTable = powerbi{[Schema="snow",Item="AllRelationshipsKeyTable"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(snow_AllRelationshipsKeyTable,{{"EOL", type date}, {"EOSL", type date}, {"LCS_GA", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "Usage_Risk_Score"}),
ChangeType = Table.TransformColumnTypes(#"Removed Other Columns",{{"Name", type text}, {"Usage_Risk_Score", Int64.Type}}),
PivotStep = Table.Pivot(#"ChangeType", List.Distinct(#"Changed Type"[Name]), "Name", "Usage_Risk_Score", List.Max)
in
PivotStep

       


      Best regards,