Pivot or Group

[Babycakes_00]

Hi all I have a data set that I need to Change the structure and format

Any suggestions to reformat this data?

Desired Outcome is

FORM ID	Date out	DateIn	ReceivalNo	ID	Name	Location	Test	Size	Comment
123	11/26/2024 00:00:00	9/11/2024 0:00	999999	1	Lisa	Action	Test
124	11/27/2024 00:00:00	11/27/2024 00:00:00	156338	Not Set	John	Action	Test	Small
125	11/27/2024 00:00:00	11/27/2024 00:00:00	156338	Not Set	Bill	Action	Test	Small

Current Format is

FORM ID	EntryID	Name	Value
123	3486	Date out	11/26/2024 00:00:00
123	3487	DateIn	9/11/2024 0:00
123	3488	ReceivalNo	999999
123	3489	ID	1
123	3490	Name	Lisa
123	3491	Location	Action
123	3492	Test	Test
123	3493	Size
123	3494	Comment
124	3606	Date out	11/27/2024 00:00:00
124	3607	DateIn	11/27/2024 00:00:00
124	3608	ReceivalNo	156338
124	3609	ID	Not Set
124	3610	Name	John
124	3611	Location	Action
124	3612	Test	Test
124	3613	Size	Small
124	3614	Comment
125	1552	Date out	11/27/2024 00:00:00
125	1553	DateIn	11/27/2024 00:00:00
125	1554	ReceivalNo	156338
125	1555	ID	Not Set
125	1556	Name	Bill
125	1557	Location	Action
125	1558	Test	Test
125	1559	Size	Small

[v-xinruzhu-msft]

Hi @Babycakes_00 

Dod the solutions dharmendars007 , Chewdata  and tackytechtom  offered help you solve the problem, if them help, you can consider to accept them as solutions so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[dharmendars007 · Accepted solution]

Hello @Babycakes_00 

In Power Query you can follow this simple steps to get the table stucture you want.

1. Select the FORM ID and Name columns.
2. Go to the Transform tab and click on Pivot Column.
3. Use Value as the "Values Column."

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Chewdata]

Hey!

This can be achieved by using the pivot function in Power query. 
To achieve this, first remove the column that makes every row unique ( [ EntryID]). 
Secondly use Table.Pivot on the name and value columns. Selecting Don't aggregate in the advanced options.

Codesnippet:

let
    Source = YOURTABLE,
    #"Removed Columns" = Table.RemoveColumns(Source,{"EntryID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value")
in
    #"Pivoted Column"

[tackytechtom]

Hi @Babycakes_00 ,

How about this? 🙂

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZA9b4MwEIb/CmKOhD/AQLe2WRJFDKFblMFClmoJ8BC3Q399z9Yl5YRFg5De072PDH4ul5wLme9yWTYKYq+9ydyXh5HzQqhCMFFmjL3EN7/uFnyN/GGGoS0CH+EV2ECczWDstx47F+D4UKiFOOzDd8m+ZRCdngzEyd40LXnYukF768JPvA5xIIiA+DA3fw9ShujtTzg8o00J8e6mycx+UYatVCxhqk6aQp6Y+pde6eKVkrKh0ENX53zWG09avpB2dJ8zLbekIZKWhuVCWj/pcaRtWlwVr1GJZ8UhL58Th3S5KQ6hKi0OW/Un7s0+roZlvSEOkSYpDst2Je76Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"FORM ID" = _t, EntryID = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FORM ID", Int64.Type}, {"EntryID", Int64.Type}, {"Name", type text}, {"Value", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"EntryID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value")
in
    #"Pivoted Column"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/