Selected columns change to rows in tables

[Justas4478]

Hi, I have table that has dates as columns and working hours in those columns rows.

Is it possible to change table to looks like this in the power query editor?

 

Please let me know if it can be done or do I need to change it in the excel file manually?.

Thanks.

[v-rzhou-msft · Accepted solution]

Hi @Justas4478 ,

There should be multiple steps for you to transform your data.

Merge [First Name] and [Surname] columns > Remove [Dept/Shift] column > Select new [Name] column and Unpivot other columns > Group [Value] by new [Name] and new [Date] columns with SUM operation.

My Sample:

Result is as below.

Whole M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5LCoAgFAXQrYTjDH35KtpAi5AGDRoaUbT//D/6gnId3MNVazZgAavhqle4GlayZTKztLkfW3raA1WLFGwsPQyIS/EjsQKkSBJ6Ie6rQBbeVoWLz+Wnvi77iLqx65Ph4RPR1+Tr4Lt4s3Ik9xX1FfV9fTwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dept/Shift" = _t, #"First Name" = _t, Surname = _t, #"2023/01/02" = _t, #"2023/01/03" = _t, #"2023/01/04" = _t, #"2023/01/05" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept/Shift", type text}, {"First Name", type text}, {"Surname", type text}, {"2023/01/02", type number}, {"2023/01/03", type number}, {"2023/01/04", type number}, {"2023/01/05", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","name","Name",Replacer.ReplaceText,{"First Name"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","surname","Surname",Replacer.ReplaceText,{"Surname"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"First Name", "Surname"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Dept/Shift"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Name", "Date"}, {{"Work Hours", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Padycosmos]

You can unpivot the date columns:

https://learn.microsoft.com/en-us/power-query/unpivot-column

https://www.youtube.com/watch?v=SLZGWy2OUPo&list=PLApPcvU5-R24K3mbxORV7T3ckVLfDjmHF&index=29