Forum Discussion

Varan_15's avatar
Varan_15
Helper III
3 years ago

Convert all the rows to column

Hi All,   I have data rowwise but i want to change into columns wise like single ID having process steps with data time so i want to convert using power query.   expected like: input 1 and ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Varan_15 ,

 

Here's the whole M codes. You can also download the attachment for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xDsIwDER/JcrcSr64ocVfwV51QhnCUKEqwO9jWJK4k3Wn93ReVw9QCOQHn/fnq+gNmkeM7BAESy2iu8rEfhsa50iPdC8tAxJwLS4qKdlJZuiPhD5ztDvvnD6VmX87TF0BodN3J0uhaIrJvHc78l4s00iLwyxQafsC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Process = _t, IN = _t, OUT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Process", type text}, {"IN", type datetime}, {"OUT", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Process"}, {{"Count", each _, type table [ID=nullable number, Process=nullable text, IN=nullable datetime, OUT=nullable datetime]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"ID", "IN", "OUT", "Index"}, {"ID", "IN", "OUT", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Index", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Process]&" IN Time"&[Index]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Process]&" OUT Time"&[Index]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom]), "Custom", "IN"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom.1]), "Custom.1", "OUT"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column1",{"input IN Time1", "input IN Time2", "reject IN Time1", "review IN Time1", "review IN Time2", "Print IN Time1", "Print OUT Time1", "input OUT Time1", "input OUT Time2", "reject OUT Time1", "review OUT Time1", "review OUT Time2"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"input IN Time1", "input IN Time2", "reject IN Time1", "review IN Time1", "review IN Time2", "Print IN Time1", "Print OUT Time1", "input OUT Time1", "input OUT Time2", "reject OUT Time1", "review OUT Time1", "review OUT Time2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Process", "Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

 

Best Regards,

Stephen Tao

 

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