Forum Discussion
Solved
Pivot or Group
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...
Hello Babycakes_00
In Power Query you can follow this simple steps to get the table stucture you want.
- Select the FORM ID and Name columns.
- Go to the Transform tab and click on Pivot Column.
- 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
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/