Combining Multiple Column Into 1 Column

[Irwan]

Hello PQ Expert,

i need an advice.

Here is my sample data

i need to change the above sample data into like below.

Thank you

[Ahmedx · Accepted solution]

you need used a Table.Unpivot

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

[shafiz_p · Accepted solution]

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

[AlienSx · Accepted solution]

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

[Irwan]

Hello @Ahmedx @shafiz_p @AlienSx @Omid_Motamedise 

superb solution, works as intended.

Thank you very much.

[Omid_Motamedise · Accepted solution]

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!

[AlienSx · Accepted solution]

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

[shafiz_p · Accepted solution]

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

[Ahmedx · Accepted solution]

you need used a Table.Unpivot

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