Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I'm connecting to a SQL server via direct query and a have a table looking like this:
Name | Time | Time Gold | Time Silver | Time Bronze | Answer Gold | Answer Silver | Answer Bronze |
John Doe | 01-01-20 | 6 | 45 | 21 | 36 | 6 | 55 |
Another | 01-01-20 | 15 | 1 | 67 | 4 | 251 | 67 |
Now I need to have the table looking like this:
Name | Time | Rank | Time | Answer |
John Doe | 01-01-20 | Gold | 6 | 36 |
John Doe | 01-01-20 | Silver | 45 | 6 |
John Doe | 01-01-20 | Bronze | 21 | 55 |
Another | 01-01-20 | Gold | 15 | 4 |
Another | 01-01-20 | Silver | 1 | 251 |
Another | 01-01-20 | Bronze | 67 | 67 |
How do I unpivot a part of the column?
Solved! Go to Solution.
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyMjAw1AUiIwMgxwyITUxBooZAwtgMKmRqqhSrE63kmJdfkpFahK7JEKQBpN7MHKQdJG0K5cbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Time = _t, #"Time Gold" = _t, #"Time Silver" = _t, #"Time Bronze " = _t, #"Answer Gold" = _t, #"Answer Silver" = _t, #"Answer Bronze " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Time", type date}, {"Time Gold", Int64.Type}, {"Time Silver", Int64.Type}, {"Time Bronze ", Int64.Type}, {"Answer Gold", Int64.Type}, {"Answer Silver", Int64.Type}, {"Answer Bronze ", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Time"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Rank"}, {"Time", "Time_1"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
#"Pivoted Column"
Just paste in into a blank query and take a look at the steps!
Br,
J
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyMjAw1AUiIwMgxwyITUxBooZAwtgMKmRqqhSrE63kmJdfkpFahK7JEKQBpN7MHKQdJG0K5cbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Time = _t, #"Time Gold" = _t, #"Time Silver" = _t, #"Time Bronze " = _t, #"Answer Gold" = _t, #"Answer Silver" = _t, #"Answer Bronze " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Time", type date}, {"Time Gold", Int64.Type}, {"Time Silver", Int64.Type}, {"Time Bronze ", Int64.Type}, {"Answer Gold", Int64.Type}, {"Answer Silver", Int64.Type}, {"Answer Bronze ", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Time"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Rank"}, {"Time", "Time_1"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
#"Pivoted Column"
Just paste in into a blank query and take a look at the steps!
Br,
J
Aah alright I see. Thank you!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |