Add alternate row data in 2 new column

[tm9918]

I have a data like below-

Name	Value
Service	1.60E+12	EPOCH Time
Service	1094329.332	Resp Time
Service	1.60E+12	EPOCH Time
Service	1065677.9	Resp Time
Service	1.60E+12	EPOCH Time
Service	960574.6272	Resp Time

and want to convert it as- 

Name	EPOCH Time	Resp Time
Service	1.60E+12	1094329
Service	1.60E+12	1065678
Service	1.60E+12	1094329
Service	1.60E+12	1065678
Service	1.60E+12	1094329

In first table under column name 'Value' I have EPOCH Time and Resp Time for same service and it's alternate. I want to convert Value column to 2 new Columns of EPOCH Time and Resp Time.

Can someone please help

[mahoneypat]

Please see the M code below with an example of how to do this with your example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKstMTlXSUTLUMzNw1TY0AjJdA/ydPRRCMnNTlWJ1UNQYWJoYG1nqGRuDlAWlFhdgVUWUSWamZubmepYUmmNpZmBqbqJnZmSO5qJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", type number}, {"Time", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Inserted Multiplication" = Table.AddColumn(#"Added Index", "Multiplication", each Number.RoundUp([Index] / 2,0), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Multiplication",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Time]), "Time", "Value"),
    #"Rounded Off" = Table.TransformColumns(#"Pivoted Column",{{"Resp Time", each Number.Round(_, 0), type number}})
in
    #"Rounded Off"

Regards,

Pat

[HotChilli]

You need to identify each pair.

In Power Query:

First, assign a name to the 3rd column.  Let's call it 'alt'

Add an Index column from 0.

Add a custom column , dividing the index by 2 (each Number.IntegerDivide([Index], 2))).

Remove the Index column.

Pivot on the 'alt', using Value column for Values.  I don't think it matters which aggregation you use.

Good luck.

[tm9918]

@HotChilli - How can I do 'dividing the index by 2 (each Number.IntegerDivide([Index], 2)))'

[HotChilli]

@tm9918 

A bit of effort required at your side.  The code is provided.  Add a custom column.  Paste some code.  Debug it if it doesn't work.

I will help you if required.