Manipulate table and move columns to be in row format

[threw001]

Hi guys,

I have the below table where I have columns going across for each day (hours worked).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0lEyAGIjIDaDsk2UYnWilQwNoFxTuDBcyghJGIFjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"Monday Hours" = _t, #"Tuesday Hours" = _t, #"Wednesday Hours" = _t, #"Thursday Hours" = _t, #"Friday Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"Monday Hours", Int64.Type}, {"Tuesday Hours", Int64.Type}, {"Wednesday Hours", Int64.Type}, {"Thursday Hours", Int64.Type}, {"Friday Hours", Int64.Type}})
in
    #"Changed Type"

I would like to change this table to the below. Employee ID is repeated going down based on each day and the no. of hours. Can you please advise?

EmployeeID	Day	Hours
1	Monday Hours	0
1	Tuesday Hours	2
1	Wednesday Hours	6
1	Thursday Hours	0
1	Friday Hours	4
10	Monday Hours	0
10	Tuesday Hours	5
10	Wednesday Hours	0
10	Thursday Hours	4
10	Friday Hours	4
12	Monday Hours	5
12	Tuesday Hours	0
12	Wednesday Hours	0
12	Thursday Hours	0
12	Friday Hours	0

Thanks

[gmsamborn · Accepted solution]

Hi @threw001 

In Power Query:

Select the EmployeeID column, right-click and Unpivot Other Columns.

[ryan_mayu]

could you pls provide some sample data?

[threw001]

Hi @ryan_mayu 

Sample data below for Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0lEyAGIjIDaDsk2UYnWilQwNoFxTuDBcyghJGIFjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"Monday Hours" = _t, #"Tuesday Hours" = _t, #"Wednesday Hours" = _t, #"Thursday Hours" = _t, #"Friday Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeID", Int64.Type}, {"Monday Hours", Int64.Type}, {"Tuesday Hours", Int64.Type}, {"Wednesday Hours", Int64.Type}, {"Thursday Hours", Int64.Type}, {"Friday Hours", Int64.Type}})
in
    #"Changed Type"