50m sprint duel sprint comparison

[arnoldasmauraga]

Hi,

I am trying to visualize data of the 50m sprint runs. Players were running in duals and I am trying to visualize in a way that once you select player it shows players and opponents time and then based on that time displays either red or green (which means that player won the sprint dual or lost it). 
Thanks in advance for your help. 

[arnoldasmauraga]

Hi all, 

Thank you for your input. The unpivoting and Petes Query worked perfectly. 

However, now I am facing other problem. 

If I put all info into the table you can clearly see the results. But I want to visualize it using name card. When the player is selected display all different results and even highlight in green or red based on who won the duel. 

Thank you for your help once again!

[BA_Pete]

Hi @arnoldasmauraga ,

Thanks for marking my answer as the solution, glad it worked for you.

I would recommend opening a new topic for the next part of your requirement providing an example of your new data structure and your required output. I think what you are asking for will be some fairly advanced DAX and that's not my forte, so best to put it back out to the community.

Best,

Pete

[arnoldasmauraga]

Hi Pete, 

Thanks for your suggestion. 

Just coming back to your code. If I want to apply the same code for anoter group of players which particular part of the group should I change? 
Also, when I player nams in the code to display only capital letters it only changes versusName collumn, but I want to change across the board so that I could keep the name consistent with other raw data I have.

Thank you for you help. 

[BA_Pete]

@arnoldasmauraga 

Not sure I fully understand your requirements here, please clarify:

1. Do you mean a completely new, separate table of players, or just new players in the existing source table? It shouldn't matter either way to be honest as there's nothing in the code that is explicit to the values in the source table.

2. Do you mean that you want all the player names to be in capital letters? If so, see updated code below with a new "Uppercased Text" step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5LDsMgDETv4nWEgMRAzoJYoJhKLEqr9HP+RjahWTAjzMzDMQLlbyWYwCs8VB/HqZXVX3S1kKYIt70Q1SIjwzaP1swakM1xfiu0140HaAUnZt2ohUUePDeotFZfwhMSGt5O95gZRQzcuOf2uaI7D2W9xf5Nc/6Z34/O8MrJM6L8ESR8XlL6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [playerName = _t, pato = _t, david = _t, freddie = _t, cedric = _t, dennis = _t, manu = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"playerName"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "versusName"}, {"Value", "playerTime"}}),
    #"Uppercased Text" = Table.TransformColumns(#"Renamed Columns",{{"playerName", Text.Upper, type text}, {"versusName", Text.Upper, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each ([playerTime] <> 0 and [playerTime] <> "0")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"playerName", "versusName"}, #"Filtered Rows", {"versusName", "playerName"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"playerTime"}, {"versusTime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Filtered Rows",{{"playerTime", type text}, {"versusTime", type text}})
in
    #"Changed Type"

Pete

[amitchandak]

@arnoldasmauraga , can you unpivot this data and try

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[BA_Pete · Accepted solution]

Hi @arnoldasmauraga ,

You can do this in Power Query as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5LDsMgDETv4nWEgMRAzoJYoJhKLEqr9HP+RjahWTAjzMzDMQLlbyWYwCs8VB/HqZXVX3S1kKYIt70Q1SIjwzaP1swakM1xfiu0140HaAUnZt2ohUUePDeotFZfwhMSGt5O95gZRQzcuOf2uaI7D2W9xf5Nc/6Z34/O8MrJM6L8ESR8XlL6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [playerName = _t, pato = _t, david = _t, freddie = _t, cedric = _t, dennis = _t, manu = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"playerName"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "versusName"}, {"Value", "playerTime"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([playerTime] <> 0 and [playerTime] <> "0")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"playerName", "versusName"}, #"Filtered Rows", {"versusName", "playerName"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"playerTime"}, {"versusTime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Filtered Rows",{{"playerTime", type number}, {"versusTime", type number}})
in
    #"Changed Type"

Go to New Source > Blank Query, then go to Advanced Editor and paste my code above over the default query code. You can then follow the steps I took to complete this.

You would still use the [playerName] column for your page slicer, but this table structure will then give you the name of who they raced against and both of their times on the same row to present as you require.

Pete

Pete

[lbendlin]

i hope your raw data is in a more palatable format?  What does its structure look like?