Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 highest number to from each list as shown in (Finish picture).
Current
Transpose
Finsh
Solved! Go to Solution.
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,
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,
Refer:https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hi @bman6074 ,
Try this code on Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKzkgtUnAPclbSUcorzclRitXBI5pfrBAMlEopzcnMS1dwSSxJBCowhcgGeTo5oigvLkjNU3DJLC4pygzLTC1X0EXiABUaEaXK0ICwMoXggsSSzMQcBc+8ktSitMTkVISrSNYHsjAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Usage_Risk_Score = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Usage_Risk_Score", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Name]), "Name", "Usage_Risk_Score", List.Max)
in
#"Pivoted Column"
Ricardo
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"
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,
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
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |