Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create start and end date columns from rows

Hi! I'm new to power query and am stuck. I was able to manually create a table that works as needed in power bi but am having trouble automating its creation based on user inputs in an excel table. I got as far as unpivoting the table, but now I have a separate row for each milestone's start and end date. I would like each milestone to have its own row with a start and end date column.

 

Unpivoted table:

 

 

Here is a screenshot of the table that works in power bi as needed:

 

 

I want to move the end date value to a column next to the start date for each milestone, for each project. If I can provide better screenshots or sample data please let me know. Looking forward to your help!!

Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    One approach would be as follows.

    1. Add a column that returns the 'Start Date' for all rows that have a Milestone that does not end with 'End'
    2. then add a column that returns the 'Start Date' for rows that do not end in 'Start'
    3. on the second created column 'Fill Up' to fill in the null values
    4. remove the 'Start' or 'End' from any rows in the 'MIlestone' column that have those as ending words
    5. filter out null values from the first created column
    6. remove the original 'Start Date' column
    7. rename created columns as needed

    As an example, starting with...

    and ending up with...

     

     

    with the code...

     

    let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZKxCoMwFEV/RTILaV6M2LFIC91ER3EIKjQgEUKkv19FWiE28blkeffm5YRT10SkQGKSl8V8cspSChcA0sTfSfkoonwyptc2qtpX301DH1VWGjsPGaOwFPhR4a47T7waB9UqK60adWARFeeK60KgiVu7vaXpdvl5ICjjSzhxWJ5aWSUHPPyugIT3LzqA9yz0wrv5HTzLxM+JjMJ1u2adnHAiUPj3LWsc6wS4T8Y5wdwa0omNBelEoICEDzgRhvc64YEPO9F8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Milestone = _t, #"Start Date" = _t]),
    changeDataTypes = 
    Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"Milestone", type text}, {"Start Date", type date}}),
    addStartDate = 
    Table.AddColumn(changeDataTypes, "sDate", each if not Text.EndsWith([Milestone], "End") then [Start Date] else null, type date),
    addEndDate = 
    Table.AddColumn(addStartDate, "eDate", each if Text.EndsWith([Milestone], "Start") then null else [Start Date], type date),
    fillUpEndDate = 
    Table.FillUp(addEndDate,{"eDate"}),
    cleanMilestones = 
    Table.TransformColumns(fillUpEndDate, {{"Milestone", each if Text.EndsWith(_, "Start") or Text.EndsWith(_, "End") then Text.BeforeDelimiter(_, " ", RelativePosition.FromEnd) else _, type text}}),
    filterRows = 
    Table.SelectRows(cleanMilestones, each ([sDate] <> null)),
    removeOriginalDate = 
    Table.RemoveColumns(filterRows,{"Start Date"}),
    renameColumns = 
    Table.RenameColumns(removeOriginalDate,{{"sDate", "Start Date"}, {"eDate", "End Date"}})
in
    renameColumns

     

     

     

     

4 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    One approach would be as follows.

    1. Add a column that returns the 'Start Date' for all rows that have a Milestone that does not end with 'End'
    2. then add a column that returns the 'Start Date' for rows that do not end in 'Start'
    3. on the second created column 'Fill Up' to fill in the null values
    4. remove the 'Start' or 'End' from any rows in the 'MIlestone' column that have those as ending words
    5. filter out null values from the first created column
    6. remove the original 'Start Date' column
    7. rename created columns as needed

    As an example, starting with...

    and ending up with...

     

     

    with the code...

     

    let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZKxCoMwFEV/RTILaV6M2LFIC91ER3EIKjQgEUKkv19FWiE28blkeffm5YRT10SkQGKSl8V8cspSChcA0sTfSfkoonwyptc2qtpX301DH1VWGjsPGaOwFPhR4a47T7waB9UqK60adWARFeeK60KgiVu7vaXpdvl5ICjjSzhxWJ5aWSUHPPyugIT3LzqA9yz0wrv5HTzLxM+JjMJ1u2adnHAiUPj3LWsc6wS4T8Y5wdwa0omNBelEoICEDzgRhvc64YEPO9F8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Milestone = _t, #"Start Date" = _t]),
    changeDataTypes = 
    Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"Milestone", type text}, {"Start Date", type date}}),
    addStartDate = 
    Table.AddColumn(changeDataTypes, "sDate", each if not Text.EndsWith([Milestone], "End") then [Start Date] else null, type date),
    addEndDate = 
    Table.AddColumn(addStartDate, "eDate", each if Text.EndsWith([Milestone], "Start") then null else [Start Date], type date),
    fillUpEndDate = 
    Table.FillUp(addEndDate,{"eDate"}),
    cleanMilestones = 
    Table.TransformColumns(fillUpEndDate, {{"Milestone", each if Text.EndsWith(_, "Start") or Text.EndsWith(_, "End") then Text.BeforeDelimiter(_, " ", RelativePosition.FromEnd) else _, type text}}),
    filterRows = 
    Table.SelectRows(cleanMilestones, each ([sDate] <> null)),
    removeOriginalDate = 
    Table.RemoveColumns(filterRows,{"Start Date"}),
    renameColumns = 
    Table.RenameColumns(removeOriginalDate,{{"sDate", "Start Date"}, {"eDate", "End Date"}})
in
    renameColumns

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you! This is exactly what I need! Can you help me with which parts I need to update to apply this to my whole data set? I'm able to recreate just your example here.

    • not_admin's avatar
      not_admin
      New Member
      2 years ago

      I've been stuck for days and this such a simple solution

      thank you

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    Sure thing.
    In the code I posted, everything from "addStartDate = ..." and down is what you would add to your existing code. You would also have to change the 'changeDataTypes' to the name of the previous step in your code.

    addStartDate =
    Table.AddColumn(changeDataTypes, "sDate", each if not Text.EndsWith([Milestone], "End") then [Start Date] else null, type date) 

    Assuming your column names are the same as what you posted, there should not be anything else to change.

    Feel free to send me a private message if you need anything further.