Creating a New Table with Specific Columns Values From an Existing Table

[Eoekid]

I currently have a Table in the following format:

ID	A_1	A_2	A_3	B_1	B_2	B_3
1	A_1_val	A_2_val	A_3_val	B_1_val	B_2_val	B_3_val
2	A_1_val	A_2_val	A_3_val	B_1_val	B_2_val	B_3_val

Is it possible to merge specific columns into rows in a new table?

Along with adding a new column, with a value specific to which group of columns were used?

If possible, I would prefer to do this using Power Query.

With a new table, that has following columns: ID, X, Y, Z.

The column tranformations I would like to do are:

'A' Columns

   ID -> ID

   A_1 -> W

   A_2 -> X

   A_3 -> Y

   Column Z, with the value "A"

'B' Columns

   ID -> ID

   B_1 -> W

   B_2 -> X

   B_3 -> Y

   Column Z, with the value "B"

The end result I want is a table with the following data:

ID	W	X	Y	Z
1	A_1_val	A_2_val	A_3_val	"A"
1	B_1_val	B_2_val	B_3_val	"B"
2	A_1_val	A_2_val	A_3_val	"A"
2	B_1_val	B_2_val	B_3_val	"B"

[ryan_mayu · Accepted solution]

@Eoekid 

Pls see if this is what you want

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKMN4wvS8wBs4zgLGMoywku6wSXdYLKxupEKxlRZkIsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, A_1 = _t, A_2 = _t, A_3 = _t, B_1 = _t, B_2 = _t, B_3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"A_1", type text}, {"A_2", type text}, {"A_3", type text}, {"B_1", type text}, {"B_2", type text}, {"B_3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Duplicated Column", {{"Attribute", each Text.End(_, 1), type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Extracted Last Characters", {{"Attribute - Copy", each Text.Start(_, 1), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted First Characters","1","W",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","X",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","3","Y",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute - Copy", "Z"}})
in
    #"Renamed Columns"