Combining Multiple Column Into 1 Column

you need used a Table.Unpivot

https://www.youtube.com/watch?v=ln2fcF-hXqo&ab_channel=HOGOLYTICS

Hi Irwan  To acheive the desired result, follow the steps below:

Using merge and unpivot technique:

1. First Merge column2, Column3 with seperator, say semicolon. Repeate this process for Column4, 5 and Column6,7. 

See images below after merge:

2. Now select Column1 and select Unpivot other columns. See image below:

3. Now split "Value" column using delimiter (Semicolon). See image below:

4. Now Change type to whole number and Sort the column "Value.1" in ascending order. See image below for your desired result:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

Table.FromRows(
    List.TransformMany(
        Table.ToRows(Source),
        (x) => List.Split(List.Skip(x), 2),
        (x, y) => {x{0}} & y
    )
)

This problem can be solved in different way, but one of the most time efficent way is :

just copy and past the below code on Advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmMgNgVicyC2BIkZKsXqRCs5AZlGQGwCxGZAbAGSMgARRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,List.Skip(Table.ColumnNames(Source)),each List.Split(_,2),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Merged", {"Merged", each Record.FromList(_,{"Column2","Column3"})}),
    #"Expanded Merged1" = Table.ExpandRecordColumn(#"Extracted Values", "Merged", {"Column2", "Column3"}, {"Column2", "Column3"})
in
    #"Expanded Merged1"

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!