Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unpivot part of column

Hello

 

I'm connecting to a SQL server via direct query and a have a table looking like this:

NameTimeTime GoldTime SilverTime Bronze Answer GoldAnswer SilverAnswer Bronze 
John Doe01-01-206452136655
Another01-01-2015167425167

 

Now I need to have the table looking like this:

NameTimeRankTimeAnswer
John Doe01-01-20Gold636
John Doe01-01-20Silver456
John Doe01-01-20Bronze2155
Another01-01-20Gold154
Another01-01-20Silver1251
Another01-01-20Bronze6767

 

How do I unpivot a part of the column?

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

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


Connect on LinkedIn

View solution in original post

2 REPLIES 2
tex628
Community Champion
Community Champion

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


Connect on LinkedIn
Anonymous
Not applicable

Aah alright I see. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.