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

[ashmitp869]

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 Defination	BL Project Start	BL Project Finish	Milestone
Contract Award	16/08/2024 0:00		M1
SWC Issue Works to Proceed	14/10/2024 0:00		M2
Confirmation of site facilities	28/10/2024 0:00		M3
Greenfields Site Classification by North Head WWTP	28/10/2024 0:00		M4
Start on Site	12/12/2024 0:00		M5
Completion of Crane Installation		20/01/2026 0:00	M6
Completion of Excavation of Cavern		21/11/2025 0:00	M7
Operational Completion		22/01/2026 0:00	M8
Project Completion		25/02/2026 0:00	M9

Regards

[danextian]

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]

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 Type	Milestone Defination	BL Project Start	BL Project Finish	Milestone
SYD038002	Start Milestone	Contract Award	16/08/2024 0:00		M1
SYD038002	Start Milestone	SWC Issue Works to Proceed	14/10/2024 0:00		M2
SYD038002	Start Milestone	Confirmation of site facilities	28/10/2024 0:00		M3
SYD038002	Start Milestone	Greenfields Site Classification by North Head WWTP	28/10/2024 0:00		M4
SYD038002	Start Milestone	Start on Site	12/12/2024 0:00		M5
SYD038002	Finish Milestone	Completion of Crane Installation		20/01/2026 0:00	M6
SYD038002	Finish Milestone	Completion of Excavation of Cavern		21/11/2025 0:00	M7
SYD038002	Finish Milestone	Operational Completion		22/01/2026 0:00	M8
SYD038002	Finish Milestone	Project Completion		25/02/2026 0:00	M9
SYD038002	Task	Activity 1
SYD038002	Task	Activity 2	2/01/2025	5/05/2025
SYD038004	Start Milestone	Contract Award	16/08/2024 0:00		M1
SYD038004	Start Milestone	SWC Issue Works to Proceed	14/10/2024 0:00		M2
SYD038004	Finish Milestone	Operational Completion		22/01/2026 0:00	M3
SYD038004	Finish Milestone	Project Completion		25/02/2026 0:00	M4

[danextian · Accepted solution]

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"

[ashmitp869]

Hi @danextian 
I am still struggling. 
Providing you a sample file.
https://github.com/suvechha/samplepbi/blob/main/p6%20(1)%20(1).pbix
What I am getting

Expected Result

[danextian]

Your sample file is useless to me as the transformation needs to be done in the query editor  which requires an access to your raw data.

[ashmitp869]

Hi @danextian 
I have managed to implement in M Code your logic.

let
    Source = Excel.Workbook(Web.Contents("https://interflowsp.sharepoint.com/Business_Information_Management/HUB/Shared%20Documents/Projects/01.%20Current%20Projects/InEight%20for%20Interflow%20(GRP)/0.2%20Planning%20-%20Requirements%20-%20Design%20Documentation/Requirements/Module%20-%20Connected%20Analytics%20(Reporting)/InEight%20Reports%20-%20Plan%20on%20a%20page%20v1/P6%20Data/PI.xlsx"), null, true),
    P6_Sheet = Source{[Item="P6",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(P6_Sheet, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Project ID] <> null) and ([Activity Type] = "Finish Milestone" or [Activity Type] = "Start Milestone")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"BL Project Start", type date}, {"BL Project Finish", type date}, {"Activity Name", type text}}),
    #"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, Activity Name=nullable text, BL Project Start=nullable datetime, BL Project Finish=nullable datetime,  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", "Activity Name", "BL Project Start", "BL Project Finish", "Date", "Index"}, {"Activity Type", "Activity Name", "BL Project Start", "BL Project Finish",  "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 Text.From([Index]) else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Milesone", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",#datetime(2200, 5, 2, 0, 0, 0),null,Replacer.ReplaceValue,{"Date"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each ([Project ID] = "SYD038-003"))
in
    #"Filtered Rows1"

But the Date is triggering the Index not considering the Activity Type that is "Start Milestone" and "Finish Milestone"

Expected order should be like 

[Akash_Varuna]

@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])