Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

Help me with transform data - create new column (Milestone - M1,M2....M8,M9)

Hi,
I have a table like below -  
How can I transform data in Power Query and create a column (Milestone) with a sequence? 
The sequence creating will be BL Project Start , BL Project End (date sort ascending)

Milestone DefinationBL Project StartBL Project FinishMilestone 
Contract Award16/08/2024 0:00 M1
SWC Issue Works to Proceed14/10/2024 0:00 M2
Confirmation of site facilities28/10/2024 0:00 M3
Greenfields Site Classification by North Head WWTP28/10/2024 0:00 M4
Start on Site12/12/2024 0:00 M5
Completion of Crane Installation 20/01/2026 0:00M6
Completion of Excavation of Cavern 21/11/2025 0:00M7
Operational Completion 22/01/2026 0:00M8
Project Completion 25/02/2026 0:00M9

 

Regards

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Please try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdPdboIwFAfwVznh2oRSwbHdGfblhZsJJmQxXnR4iJ2VmrZz8218Fp9sBcVFcREzk17ACfz+J6ftaOTEb/ekHRJCnZYTG6YM9LlAbWSOthLJ3CiWGuh+MTWxBa/jktClhPpA7gixFbv6njNunaPiJIKe1p8IiVQzDUbCQMkUsWR91yM1ljZgbYcZV3NmuMxBZqC5QchYygU3HLX9goan7HYD+0khWh3FRENcuJFgWvOMp9u49xW8SGWm8IxsAkkyHPwZ5zeZUFmxbpFVDIW6dh1LQU165DnX06OpzBcCq5lEiuUIvVwbJkTZ+taixCVeEdCpAvqdy/WH75Qt9/OP2BJV5XuuV/rB3r9p4r8uUJUgE/CbtTNpreewiWnP2gfag1zzApfQQ++25g2Znm3W9qGbGr7kZgXeZl0Wduv8D0WVVL0HxYsNDqqXA8C/3lU8Tf37KvpX2bd2E/OCfbN3bPwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID " = _t, #"Activity Type" = _t, #"Milestone Defination" = _t, #"BL Project Start" = _t, #"BL Project Finish" = _t, #"Milestone " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Milestone Defination", type text}, {"BL Project Start", type datetime}, {"BL Project Finish", type datetime}, {"Milestone ", type text}}, "en-au"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each if [Activity Type] = "Start Milestone" or [Activity Type] = "Finish Milestone" then 
[BL Project Start]??[BL Project Finish] else #datetime(2200, 5, 2, 0, 0, 0), type date),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom",{{"Date", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Project ID "}, {{"Grouped", each _, type table [#"Project ID "=nullable text, Activity Type=nullable text, Milestone Defination=nullable text, BL Project Start=nullable datetime, BL Project Finish=nullable datetime, #"Milestone "=nullable text, Date=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([Grouped], "Index", 1,1)),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Added Custom1", "Added Index", {"Activity Type", "Milestone Defination", "BL Project Start", "BL Project Finish", "Milestone ", "Date", "Index"}, {"Activity Type", "Milestone Defination", "BL Project Start", "BL Project Finish", "Milestone ", "Date", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Grouped"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Milesone", each if [Activity Type] = "Start Milestone" or [Activity Type] = "Finish Milestone" then "M" & Text.From([Index]) else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2",#datetime(2200, 5, 2, 0, 0, 0),null,Replacer.ReplaceValue,{"Date"})
in
    #"Replaced Value"

     

     

7 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    ashmitp869 Could you follow these please 

    1. Add a Custom Column for Sorting

      • Go to the Add Column tab and select Custom Column.

      • Name the column SortDate and use the following formula:

        if [BL Project Start] <> null then [BL Project Start] else [BL Project Finish]

      • This creates a unified column to sort dates.

    2. Sort Rows bySortDate

      • Click on the SortDate column header and choose Sort Ascending.

    3. Add an Index Column

      • Go to the Add Column tab and select Index Column > From 1.

    4. Create theMilestoneColumn

      • Add another Custom Column with the formula:

        "M" & Text.From([Index])
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi ashmitp869 

    Create a custom column to coalesce BL Project Start  and BL Project End

    [BL Project Start]??[BL Project Finish]

    Arrange the newly added column in ascending order and then wrap the step using Table.Buffer. This ensures the sorting is retained in memory; otherwise, when you load the query, the records will be arranged based on their original sequence in the data source.

    = Table.Buffer(Table.Sort(#"Added Custom",{{"Date", Order.Ascending}}))

    Add an Index column starting 1 and create another custom column.

    = Table.AddColumn(#"Added Index", "Milestone", each "M" & Text.From([Index]))

    Here's the full M

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHBTsMwDIZfxcp5UtLQlsINVQh2GEwaUg9TD6Z1RSBrpiQMeHvSdB2CDsmnyN/32/F2y0rTe4uNh5sPtC1bsCTnouBSyBTEtRDhJdQqYfViyzZVCUvn3gkqY98ceANraxqiCKY8ETNQRjCkdMru0CvTg+nAKU/QYaO08opcaJTFOfoi0neWKPCkWwebgSw1Oqc61YzC5y94MNa/wD1hC1X1tP5XmI57eLQeAjnYhtElD/W3NzuOvttrmgYvLfYEy9551Dqmj81ScJEMhnwyrPIz+O1ng4fTL5R4IDsJEp5EQXYSXEbB455sJFDDj+wIyVlqEaFwlVcKR50BGRfyN3DF6vob", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Milestone Defination" = _t, #"BL Project Start" = _t, #"BL Project Finish" = _t, #"Milestone " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Milestone Defination", type text}, {"BL Project Start", type datetime}, {"BL Project Finish", type datetime}, {"Milestone ", type text}}, "en-au"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each [BL Project Start]??[BL Project Finish], type date),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom",{{"Date", Order.Ascending}})),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Milestone", each "M" & Text.From([Index]), type text)
in
    #"Added Custom1"

     

     

    • ashmitp869's avatar
      ashmitp869
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      Hi danextian ,
      Thanks for the above solution. 
      After doing your recomended steps - I realised that I missed two columns to bring into consideration.
      Project ID and Activity Type.


      I am expecting the below results -
      The index should work when Actvity Type is "Start Milestone" and "Finish Milestone" against each Project ID

      Project ID Activity TypeMilestone DefinationBL Project StartBL Project FinishMilestone 
      SYD038002Start MilestoneContract Award16/08/2024 0:00 M1
      SYD038002Start MilestoneSWC Issue Works to Proceed14/10/2024 0:00 M2
      SYD038002Start MilestoneConfirmation of site facilities28/10/2024 0:00 M3
      SYD038002Start MilestoneGreenfields Site Classification by North Head WWTP28/10/2024 0:00 M4
      SYD038002Start MilestoneStart on Site12/12/2024 0:00 M5
      SYD038002Finish MilestoneCompletion of Crane Installation 20/01/2026 0:00M6
      SYD038002Finish MilestoneCompletion of Excavation of Cavern 21/11/2025 0:00M7
      SYD038002Finish MilestoneOperational Completion 22/01/2026 0:00M8
      SYD038002Finish MilestoneProject Completion 25/02/2026 0:00M9
      SYD038002Task Activity 1     
      SYD038002Task Activity 22/01/20255/05/2025 
      SYD038004Start MilestoneContract Award16/08/2024 0:00 M1
      SYD038004Start MilestoneSWC Issue Works to Proceed14/10/2024 0:00 M2
      SYD038004Finish MilestoneOperational Completion 22/01/2026 0:00M3
      SYD038004Finish MilestoneProject Completion 25/02/2026 0:00M4
      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        Please try this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdPdboIwFAfwVznh2oRSwbHdGfblhZsJJmQxXnR4iJ2VmrZz8218Fp9sBcVFcREzk17ACfz+J6ftaOTEb/ekHRJCnZYTG6YM9LlAbWSOthLJ3CiWGuh+MTWxBa/jktClhPpA7gixFbv6njNunaPiJIKe1p8IiVQzDUbCQMkUsWR91yM1ljZgbYcZV3NmuMxBZqC5QchYygU3HLX9goan7HYD+0khWh3FRENcuJFgWvOMp9u49xW8SGWm8IxsAkkyHPwZ5zeZUFmxbpFVDIW6dh1LQU165DnX06OpzBcCq5lEiuUIvVwbJkTZ+taixCVeEdCpAvqdy/WH75Qt9/OP2BJV5XuuV/rB3r9p4r8uUJUgE/CbtTNpreewiWnP2gfag1zzApfQQ++25g2Znm3W9qGbGr7kZgXeZl0Wduv8D0WVVL0HxYsNDqqXA8C/3lU8Tf37KvpX2bd2E/OCfbN3bPwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID " = _t, #"Activity Type" = _t, #"Milestone Defination" = _t, #"BL Project Start" = _t, #"BL Project Finish" = _t, #"Milestone " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Milestone Defination", type text}, {"BL Project Start", type datetime}, {"BL Project Finish", type datetime}, {"Milestone ", type text}}, "en-au"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each if [Activity Type] = "Start Milestone" or [Activity Type] = "Finish Milestone" then 
[BL Project Start]??[BL Project Finish] else #datetime(2200, 5, 2, 0, 0, 0), type date),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom",{{"Date", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Project ID "}, {{"Grouped", each _, type table [#"Project ID "=nullable text, Activity Type=nullable text, Milestone Defination=nullable text, BL Project Start=nullable datetime, BL Project Finish=nullable datetime, #"Milestone "=nullable text, Date=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([Grouped], "Index", 1,1)),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Added Custom1", "Added Index", {"Activity Type", "Milestone Defination", "BL Project Start", "BL Project Finish", "Milestone ", "Date", "Index"}, {"Activity Type", "Milestone Defination", "BL Project Start", "BL Project Finish", "Milestone ", "Date", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Grouped"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Milesone", each if [Activity Type] = "Start Milestone" or [Activity Type] = "Finish Milestone" then "M" & Text.From([Index]) else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2",#datetime(2200, 5, 2, 0, 0, 0),null,Replacer.ReplaceValue,{"Date"})
in
    #"Replaced Value"