Forum Discussion
Pivot to get vertical table?
Working with a collection of golf score data, I have:
Player | Course | Layout |....| Hole 1| Hole 2 | Hole 3 etc across the columns.
I am trying to create a typical handicapping table where the user selects a Course via filter, and the visual then shows these columns with the associated data running down from 1 to 18.
Hole # | Average | Std. Dev
I can get this as a horizontal matrix, but it's pretty long and ugly.
I'm thinking I need to pivot this to pick up the Hole # as rows, but I'm a bit lost. I've attached a sample file in case anyone is bored enough to play with it. Grab the CSV here.
Thanks in advance.
You should unpivot these data to get data into the same columns for easier analysis. You can then use the new Hole column on rows or columns in your matrix visual. Here is a query for one good way to transform your data for easy analysis/visualization. Paste the code in a blank query and update the path in the Source line to point to the csv file on your computer.
let Source = Csv.Document(File.Contents("C:\Users\pat\Downloads\UDiscSample.csv"),[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PlayerName", type text}, {"CourseName", type text}, {"LayoutName", type text}, {"Date", type datetime}, {"Total", Int64.Type}, {"+/-", Int64.Type}, {"Hole1", Int64.Type}, {"Hole2", Int64.Type}, {"Hole3", Int64.Type}, {"Hole4", Int64.Type}, {"Hole5", Int64.Type}, {"Hole6", Int64.Type}, {"Hole7", Int64.Type}, {"Hole8", Int64.Type}, {"Hole9", Int64.Type}, {"Hole10", Int64.Type}, {"Hole11", Int64.Type}, {"Hole12", Int64.Type}, {"Hole13", Int64.Type}, {"Hole14", Int64.Type}, {"Hole15", Int64.Type}, {"Hole16", Int64.Type}, {"Hole17", Int64.Type}, {"Hole18", Int64.Type}, {"Hole19", Int64.Type}, {"Hole20", Int64.Type}, {"Hole21", Int64.Type}, {"Hole22", Int64.Type}, {"Hole23", Int64.Type}, {"Hole24", Int64.Type}, {"Hole25", Int64.Type}, {"Hole26", Int64.Type}, {"Hole27", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Total", "+/-"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"PlayerName", "CourseName", "LayoutName", "Date"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Hole","",Replacer.ReplaceText,{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Hole"}, {"Value", "Score"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}) in #"Changed Type1"Regards,
Pat
3 Replies
- mahoneypat
Microsoft Employee
You should unpivot these data to get data into the same columns for easier analysis. You can then use the new Hole column on rows or columns in your matrix visual. Here is a query for one good way to transform your data for easy analysis/visualization. Paste the code in a blank query and update the path in the Source line to point to the csv file on your computer.
let Source = Csv.Document(File.Contents("C:\Users\pat\Downloads\UDiscSample.csv"),[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PlayerName", type text}, {"CourseName", type text}, {"LayoutName", type text}, {"Date", type datetime}, {"Total", Int64.Type}, {"+/-", Int64.Type}, {"Hole1", Int64.Type}, {"Hole2", Int64.Type}, {"Hole3", Int64.Type}, {"Hole4", Int64.Type}, {"Hole5", Int64.Type}, {"Hole6", Int64.Type}, {"Hole7", Int64.Type}, {"Hole8", Int64.Type}, {"Hole9", Int64.Type}, {"Hole10", Int64.Type}, {"Hole11", Int64.Type}, {"Hole12", Int64.Type}, {"Hole13", Int64.Type}, {"Hole14", Int64.Type}, {"Hole15", Int64.Type}, {"Hole16", Int64.Type}, {"Hole17", Int64.Type}, {"Hole18", Int64.Type}, {"Hole19", Int64.Type}, {"Hole20", Int64.Type}, {"Hole21", Int64.Type}, {"Hole22", Int64.Type}, {"Hole23", Int64.Type}, {"Hole24", Int64.Type}, {"Hole25", Int64.Type}, {"Hole26", Int64.Type}, {"Hole27", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Total", "+/-"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"PlayerName", "CourseName", "LayoutName", "Date"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Hole","",Replacer.ReplaceText,{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Hole"}, {"Value", "Score"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}) in #"Changed Type1"Regards,
Pat
Thank you very much! I appreciate the help. I've still got some work to do, but that's the table I was after!
