Forum Discussion

Responsive 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

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"

ashmitp869

Responsive 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 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

Super 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"

ashmitp869

Responsive Resident

1 year ago

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

Super User

1 year ago

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

Responsive Resident

1 year ago

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

Forum Discussion

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

Related content

Table Transformation

Transform columns / type

Data transform using power query

Table Transformation

M Language Transformations