Create new table with new data organization

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!