Combining eight columns into four

Anonymous 

Solution I:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxbCoAwDETRveS7wiRtfayldP/bsGpB0Av5yYG5rZlLsmTyZVwormdYAdunxWse0/JjPd1Fh3X9r3VAMWMxoLjCWmAFixmKG6wdrH6K/QQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Date = _t, Value_1 = _t, Date_1 = _t, Value_2 = _t, Date_2 = _t, Value_3 = _t, Date_3 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "V"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each Text.Combine(_[V],"#(tab)")}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "All", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Value", "Date"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Value", Order.Ascending}})
in
    #"Sorted Rows"

Solution II, which borrowed Jimmy801 's elegant solution but with a bit amendment,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxbCoAwDETRveS7wiRtfayldP/bsGpB0Av5yYG5rZlLsmTyZVwormdYAdunxWse0/JjPd1Fh3X9r3VAMWMxoLjCWmAFixmKG6wdrH6K/QQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Date = _t, Value_1 = _t, Date_1 = _t, Value_2 = _t, Date_2 = _t, Value_3 = _t, Date_3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Date", type date}, {"Value_1", Int64.Type}, {"Date_1", type date}, {"Value_2", Int64.Type}, {"Date_2", type date}, {"Value_3", Int64.Type}, {"Date_3", type date}}),

    ListSplit = List.Split(Table.ColumnNames(#"Changed Type"), 2),
    CreateTableGroups = List.Transform(ListSplit,
        each Table.FromColumns(List.Accumulate({0..List.Count(_)-1}, {}, (s,c)=>s&{Table.Column(#"Changed Type", _{c})}), {"Date","Value"})
    ),
    TableCombine = Table.Combine(CreateTableGroups)
in
    TableCombine

Hi, Anonymous , you might follow these steps,

1. Import your own actual data into PQ;
2. Copy any one of solutions proposed here ( code after the step "Source");
3. Open up Advanced Editor, append the code proposed after your own step "Source";

Hello Anonymous 

you have to dig in the M-code to make things work. You can create your query (read from Workbook) and goto the advanved editor and replace your in statement with this code (Add a "," after your your code before pasting. Subsitute the #"Changed Type" with your last stepname. 

    ListSplit = List.Split(Table.ColumnNames(#"Changed Type"), 2),
    CreateTableGroups = List.Transform( ListSplit, (lst)=> Table.FromColumns({Table.Column(#"Changed Type", lst{0}),Table.Column(#"Changed Type", lst{1})}, {"Value", "Date"})),
    TableCombine = Table.Combine(CreateTableGroups)
in
    TableCombine

this is the most straightforward you can do. You can also use a more professional way to handle it, to create a new function like this Anonymous mentioned it already. Create a new blank query and copy paste this code there an name the query TransformYourTable

(tbl as table) =>
let

    ListSplit = List.Split(Table.ColumnNames(tbl), 2),
    CreateTableGroups = List.Transform( ListSplit, (lst)=> Table.FromColumns({Table.Column(tbl, lst{0}),Table.Column(tbl, lst{1})}, {"Value", "Date"})),
    TableCombine = Table.Combine(CreateTableGroups)
in
    TableCombine

then go to your query from the workbook, click on the table-symbol and select "invoke custom function"

Right click on your last step and select "Insert Step After"

go to the formula bar and put your new custom function

Before

After

Hope this helps

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy