Convert all the rows to column

[Varan_15]

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 input 2 .... like all the process steps

Thanks,

[Anonymous]

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.

[Varan_15]

Hi @Anonymous,

I have the bulk records with same process steps hence when we apply index() function it's generating 1-till record counts.

example records generating like input 1 input 2  input3 till total count........ attached sample raw file kindly explore this.

ID	Process 	IN	              OUT
11011	input	09/Jan/2022 14:58:01	10/Jan/2022 09:55:02
11011	reject	02/Jan/2022 10:32:12	06/Jan/2022 13:40:49
11011	input	06/Jan/2022 13:40:58	06/Jan/2022 14:56:42
110200	input	02/Jan/2022 08:33:43	02/Jan/2022 08:33:43
110200	reject	02/Jan/2022 08:33:49	05/Jan/2022 15:07:17
110200	input	05/Jan/2022 15:07:40	06/Jan/2022 07:44:53
110200	closed	06/Jan/2022 07:45:17	06/Jan/2022 11:02:49
110220	input	30/Mar/2022 15:19:52	30/Mar/2022 15:19:52
110220	reject	30/Mar/2022 15:20:04	31/Mar/2022 10:29:23
110220	input	31/Mar/2022 10:29:25	31/Mar/2022 10:53:39
110220	closed	31/Mar/2022 10:54:00	27/Nov/2022 17:01:19

[Varan_15]

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,

[ppm1]

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.

Pat