Convert all the rows to column

Your proposed structure isn't great for analysis. Instead, you should unpivot the IN/OUT columns. You can then get your desired view by using both the Process and new unpivoted IN/OUT column in the Columns field well of a matrix visual. I couldn't easily extract your sample data as an image, so couldn't provide a specific example/demo.

Hi ppm1 ,

Thanks for your suggesstions actually i have to create cal column for all the process steps IN-OUT time taken hours to calculate process SLA logic.
Hence we would like to convert into rows to column in power query not in a matrix visual table. all the rows should be convert into columns for calculation /measure purpose. please suggest

thanks,

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.