Data Transformation Help

[nsexton12]

Hello,

I am new to Power Query and need a little help. Below are two tables with the first represententing how my data is currently structured, and the second representing how I need to to be transformed. I know that I can create separate tables for each pair (one for A, one for B, etc.), and then append them all together at the end with the matching columns. However, I wanted to see if there is a more efficient way to do it? 

Name	A-K	A-E	B-K	B-E	C-K	C-E	D-K	D-E
Tyler	1.1	3.1	1.7	1.5	4.7	1.2	4.5	3.0
Sarah	3.4	2.4	3.9	2.9	3.5	1.5	2.5	2.1
Lara	2.6	4.9	4.1	3.6	2.9	2.4	1.5	2.9
Jimmy	3.1	5.0	1.1	4.1	1.6	3.9	3.5	1.1

Name	“Attribute”	K	E
Tyler	A	1.1	3.1
Sarah	A	3.4	2.4
Lara	A	2.6	4.9
Jimmy	A	3.1	5.0
Tyler	B	1.7	1.5
Sarah	B	3.9	2.9

[edhans · Accepted solution]

See if this is what you need

Here is what I did:

Selected the Name column and unpivoted other columns. I get this:

I then selected the Attribute column, Transform Ribbon, Split Column by the - character.

Change Value column to a number, then I selecgted the Attribute.2 column, and on the transform ribbon, Pivot Column with these settings:

You can then rename the columns to what you need them to be.

Full code sample here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47CoAwEETvsrUENj/JFcROu5AihaCgjZ23dx2TbR6TwJudnGl9zu2mgdiw0IFsRjAIfcsW+ftxVIZMS73rDsMLLehMQk7IQTtsI8OcxcQzojKB//Go+l/Y9QRxOq7r0ZFBR/s2OuqEflwOlhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"A-K" = _t, #"A-E" = _t, #"B-K" = _t, #"B-E" = _t, #"C-K" = _t, #"C-E" = _t, #"D-K" = _t, #"D-E" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Currency.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.