Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to Solution.
Hello @Babycakes_00
In Power Query you can follow this simple steps to get the table stucture you want.
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
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
Hello @Babycakes_00
In Power Query you can follow this simple steps to get the table stucture you want.
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
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"
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
20 | |
20 | |
10 | |
10 | |
10 |