Help with an odd transpose query please

[Anonymous]

Hi,

I have a weird data source that contains user data. There are mulitple rows for each user (not always the same number of rows) as they have each row for a new tag. I cannot alter the source.

What would be the best way to transpose these data tags into columns, and then delete the excess rows so each user has only one row? I can do this in Excel with vlookup and a bit of cleaning up but I cant figure out a good solution in PowerQuery.

Thanks in advance 🙂

Here is an example:

User ID	First Name	Last Name	Tag	Value
1234	User 1	User 1	Text tag 1	Text here needs to be in column called Text tag 1
1234	User 1	User 1	Text tag 2	Text here needs to be in column called Text tag 2
1234	User 1	User 1	Text tag 3	Text here needs to be in column called Text tag 3
1234	User 1	User 1	Text tag 4	Text here needs to be in column called Text tag 4
1234	User 1	User 1	Text tag 5	Text here needs to be in column called Text tag 5
1234	User 1	User 1	Text tag 6	Text here needs to be in column called Text tag 6
1234	User 1	User 1	Text tag 7	Text here needs to be in column called Text tag 7
1234	User 1	User 1	Text tag 8	Text here needs to be in column called Text tag 8
1234	User 1	User 1	Text tag 9	Text here needs to be in column called Text tag 9
1234	User 1	User 1	Text tag 10	Text here needs to be in column called Text tag 10
1234	User 1	User 1	Text tag 11	Text here needs to be in column called Text tag 11
1234	User 1	User 1	Text tag 12	Text here needs to be in column called Text tag 12
1234	User 1	User 1	Text tag 13	Text here needs to be in column called Text tag 13
1234	User 1	User 1	Text tag 14	Text here needs to be in column called Text tag 14
1235	User 2	User 2	Text tag 1	Text here needs to be in column called Text tag 1
1235	User 2	User 2	Text tag 2	Text here needs to be in column called Text tag 2
1235	User 2	User 2	Text tag 3	Text here needs to be in column called Text tag 3
1235	User 2	User 2	Text tag 4	Text here needs to be in column called Text tag 4
1235	User 2	User 2	Text tag 5	Text here needs to be in column called Text tag 5
1235	User 2	User 2	Text tag 6	Text here needs to be in column called Text tag 6
1235	User 2	User 2	Text tag 7	Text here needs to be in column called Text tag 7
1235	User 2	User 2	Text tag 8	Text here needs to be in column called Text tag 8
1235	User 2	User 2	Text tag 9	Text here needs to be in column called Text tag 9
1235	User 2	User 2	Text tag 10	Text here needs to be in column called Text tag 10
1235	User 2	User 2	Text tag 11	Text here needs to be in column called Text tag 11
1235	User 2	User 2	Text tag 12	Text here needs to be in column called Text tag 12
1235	User 2	User 2	Text tag 13	Text here needs to be in column called Text tag 13
1235	USer 2	USer 2	Text tag 14	Text here needs to be in column called Text tag 14
1236	User 3	User 3	Text tag 1	Text here needs to be in column called Text tag 1
1236	User 3	User 3	Text tag 2	Text here needs to be in column called Text tag 2
1236	User 3	User 3	Text tag 3	Text here needs to be in column called Text tag 3
1236	User 3	User 3	Text tag 4	Text here needs to be in column called Text tag 4
1236	User 3	User 3	Text tag 5	Text here needs to be in column called Text tag 5
1236	User 3	User 3	Text tag 6	Text here needs to be in column called Text tag 6
1237	User 4	User 4	Text tag 1	Text here needs to be in column called Text tag 1
1237	User 4	User 4	Text tag 2	Text here needs to be in column called Text tag 2
1237	User 4	User 4	Text tag 3	Text here needs to be in column called Text tag 3
1237	User 4	User 4	Text tag 4	Text here needs to be in column called Text tag 4
1237	User 4	User 4	Text tag 5	Text here needs to be in column called Text tag 5
1237	User 4	User 4	Text tag 6	Text here needs to be in column called Text tag 6
1237	User 4	User 4	Text tag 7	Text here needs to be in column called Text tag 7
1237	User 4	User 4	Text tag 8	Text here needs to be in column called Text tag 8
1237	User 4	User 4	Text tag 9	Text here needs to be in column called Text tag 9
1237	User 4	User 4	Text tag 10	Text here needs to be in column called Text tag 10
1237	User 4	User 4	Text tag 11	Text here needs to be in column called Text tag 11
1237	User 4	User 4	Text tag 12	Text here needs to be in column called Text tag 12
1237	User 4	User 4	Text tag 13	Text here needs to be in column called Text tag 13
1237	User 4	User 4	Text tag 14	Text here needs to be in column called Text tag 14
1237	User 4	User 4	Text tag 15	Text here needs to be in column called Text tag 15
1237	User 4	User 4	Text tag 16	Text here needs to be in column called Text tag 16
1237	User 4	User 4	Text tag 17	Text here needs to be in column called Text tag 17

[AlB · Accepted solution]

Hi @Anonymous 

Just pivot the Tag column. Place the following M code in a blank query to see the steps. I would stick to the format you currently have; it's most likely better

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdXNCoJQEAXgV7m4duH4W89RrcKF1aUCU1CDHr8IrAstzjDHVo4LP1zMObPfR5JmeRRHu9EPTsJh6x+Tm5rz9+XiB+8670+jm3p38O7auWPf3m+vR9O2/uSCb+pYY6cGO1XamcHOlHZusHOlXRjsQmmXBrtU2pXBrpT2ymCvlPbaYK+VtiSW8CRa3RRNbTbFEk7RplMs8RRtPsUSUPkktJjRNByWaURgU40IbKoRgU01IrCpRgQ21YjAphoR2FQjAptqRJQdrhGRzjUi0rlGRDrXiG99M+ubH51rxHL+5SwclmlEYFONCGyqEYFNNSKwqUYENtWI1Uzm4bDMngCb2hNgU3sCbGpPgE3tCbD/uSfU5QQ2dTmBTV1OlB3uciKdu5xI5y4n0rnLiXTuciLdElHRZlQsIRV1m1ti+vqorp8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"First Name" = _t, #"Last Name" = _t, Tag = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Tag", type text}, {"Value", type text}}),

    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Tag]), "Tag", "Value")
in
    #"Pivoted Column"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.