Help with an odd transpose query please

Question

Hi,&nbsp;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.&nbsp;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.&nbsp;Thanks in advance 🙂&nbsp;Here is an example:User IDFirst NameLast NameTagValue1234User 1User 1Text tag 1Text here needs to be in column called Text tag 11234User 1User 1Text tag 2Text here needs to be in column called Text tag 21234User 1User 1Text tag 3Text here needs to be in column called Text tag 31234User 1User 1Text tag 4Text here needs to be in column called Text tag 41234User 1User 1Text tag 5Text here needs to be in column called Text tag 51234User 1User 1Text tag 6Text here needs to be in column called Text tag 61234User 1User 1Text tag 7Text here needs to be in column called Text tag 71234User 1User 1Text tag 8Text here needs to be in column called Text tag 81234User 1User 1Text tag 9Text here needs to be in column called Text tag 91234User 1User 1Text tag 10Text here needs to be in column called Text tag 101234User 1User 1Text tag 11Text here needs to be in column called Text tag 111234User 1User 1Text tag 12Text here needs to be in column called Text tag 121234User 1User 1Text tag 13Text here needs to be in column called Text tag 131234User 1User 1Text tag 14Text here needs to be in column called Text tag 141235User 2User 2Text tag 1Text here needs to be in column called Text tag 11235User 2User 2Text tag 2Text here needs to be in column called Text tag 21235User 2User 2Text tag 3Text here needs to be in column called Text tag 31235User 2User 2Text tag 4Text here needs to be in column called Text tag 41235User 2User 2Text tag 5Text here needs to be in column called Text tag 51235User 2User 2Text tag 6Text here needs to be in column called Text tag 61235User 2User 2Text tag 7Text here needs to be in column called Text tag 71235User 2User 2Text tag 8Text here needs to be in column called Text tag 81235User 2User 2Text tag 9Text here needs to be in column called Text tag 91235User 2User 2Text tag 10Text here needs to be in column called Text tag 101235User 2User 2Text tag 11Text here needs to be in column called Text tag 111235User 2User 2Text tag 12Text here needs to be in column called Text tag 121235User 2User 2Text tag 13Text here needs to be in column called Text tag 131235USer 2USer 2Text tag 14Text here needs to be in column called Text tag 141236User 3User 3Text tag 1Text here needs to be in column called Text tag 11236User 3User 3Text tag 2Text here needs to be in column called Text tag 21236User 3User 3Text tag 3Text here needs to be in column called Text tag 31236User 3User 3Text tag 4Text here needs to be in column called Text tag 41236User 3User 3Text tag 5Text here needs to be in column called Text tag 51236User 3User 3Text tag 6Text here needs to be in column called Text tag 61237User 4User 4Text tag 1Text here needs to be in column called Text tag 11237User 4User 4Text tag 2Text here needs to be in column called Text tag 21237User 4User 4Text tag 3Text here needs to be in column called Text tag 31237User 4User 4Text tag 4Text here needs to be in column called Text tag 41237User 4User 4Text tag 5Text here needs to be in column called Text tag 51237User 4User 4Text tag 6Text here needs to be in column called Text tag 61237User 4User 4Text tag 7Text here needs to be in column called Text tag 71237User 4User 4Text tag 8Text here needs to be in column called Text tag 81237User 4User 4Text tag 9Text here needs to be in column called Text tag 91237User 4User 4Text tag 10Text here needs to be in column called Text tag 101237User 4User 4Text tag 11Text here needs to be in column called Text tag 111237User 4User 4Text tag 12Text here needs to be in column called Text tag 121237User 4User 4Text tag 13Text here needs to be in column called Text tag 131237User 4User 4Text tag 14Text here needs to be in column called Text tag 141237User 4User 4Text tag 15Text here needs to be in column called Text tag 151237User 4User 4Text tag 16Text here needs to be in column called Text tag 161237User 4User 4Text tag 17Text here needs to be in column called Text tag 17

alb · Accepted Answer

Hi Anonymous&nbsp;
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"
&nbsp;

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

&nbsp;

Forum Discussion

Help with an odd transpose query please

1 Reply

Related content

Help Transposing

Transpose & Pivot using Power Query

Transpose data

Table transpose in Power Query

Transpose? Query...

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