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 so...
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"

     

     

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"