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

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

    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's avatar
      ashmitp869
      Icon for Responsive Resident rankResponsive 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