Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
Sorry for the unclear title, but no idea how to describe my request.
I'd like to create a new Table in Desktop.
In my table ("Table1"), I currently have a table like:
ProjectID | Action1_Description | Action1_DueDate | Action2_Description | Action2_DueDate | Action3_Description | Action3_DueDate |
P1 | Do XYZ | 25.04.2023 | Develop TOF | 03.05.2023 | Restart TKD | 15.05.2023 |
P2 | Do MNP | 30.04.2023 | Develop FSE | 03.05.2023 | ||
P3 | Do ERT | 12.05.2023 | Develop RTG | 20.05.2023 | Restart LSN | 01.06.2023 |
And the one I would like to create would be like:
ProjectID | Action_Description | Action_DueDate |
P1 | Do XYZ | 25.04.2023 |
P1 | Develop TOF | 03.05.2023 |
P1 | Restart TKD | 15.05.2023 |
P2 | Do MNP | 30.04.2023 |
P2 | Develop FSE | 00.01.1900 |
P2 | ||
P3 | Do ERT | 12.05.2023 |
P3 | Develop RTG | 20.05.2023 |
P3 | Restart LSN | 01.06.2023 |
Would someone know how to proceed?
Fab
Solved! Go to Solution.
@Fab117 you need to transform your data in the PQ to get the output you are looking for. Start a new query and click advanced editor and paste the following M code which will do the transform and you can apply the same steps to your data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyyVeIiIwCMoxM9QxM9IwMjIxBoqllqTn5BQoh/m5AnoGxnoEpTCootbgksahEIcTbBcgzNIVLxeoAjTSCGOnrFwBkGBtgMdIt2BXdSAUwBus3huh3DQoBGW6EpAimPyjEHeRaAyxOUvAJ9gOZbahnYAZ1UywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Action1_Description = _t, Action1_DueDate = _t, Action2_Description = _t, Action2_DueDate = _t, Action3_Description = _t, Action3_DueDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"Action1_Description", type text}, {"Action1_DueDate", type text}, {"Action2_Description", type text}, {"Action2_DueDate", type text}, {"Action3_Description", type text}, {"Action3_DueDate", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProjectID"}, "Attribute", "Value"),
#"Added Action" = Table.AddColumn(#"Unpivoted Other Columns", "Action", each Text.BeforeDelimiter([Attribute],"_"), type text),
#"Added Type" = Table.AddColumn(#"Added Action", "Type", each Text.AfterDelimiter([Attribute],"_"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Type",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value")
in
#"Pivoted Column"
Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Fab117 you need to transform your data in the PQ to get the output you are looking for. Start a new query and click advanced editor and paste the following M code which will do the transform and you can apply the same steps to your data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyyVeIiIwCMoxM9QxM9IwMjIxBoqllqTn5BQoh/m5AnoGxnoEpTCootbgksahEIcTbBcgzNIVLxeoAjTSCGOnrFwBkGBtgMdIt2BXdSAUwBus3huh3DQoBGW6EpAimPyjEHeRaAyxOUvAJ9gOZbahnYAZ1UywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Action1_Description = _t, Action1_DueDate = _t, Action2_Description = _t, Action2_DueDate = _t, Action3_Description = _t, Action3_DueDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"Action1_Description", type text}, {"Action1_DueDate", type text}, {"Action2_Description", type text}, {"Action2_DueDate", type text}, {"Action3_Description", type text}, {"Action3_DueDate", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProjectID"}, "Attribute", "Value"),
#"Added Action" = Table.AddColumn(#"Unpivoted Other Columns", "Action", each Text.BeforeDelimiter([Attribute],"_"), type text),
#"Added Type" = Table.AddColumn(#"Added Action", "Type", each Text.AfterDelimiter([Attribute],"_"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Type",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value")
in
#"Pivoted Column"
Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you
User | Count |
---|---|
84 | |
69 | |
69 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |