Forum Discussion

pierre415's avatar
pierre415
Icon for Helper I rankHelper I
9 years ago
Solved

Turn Start Date & End Date columns into one column with

Hey everyone,   I have 2 columns (Start Date and End date), each one specifying a different project, that I need to combine into one column containing all dates for each project. The column should ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi pierre415,

     

    You can follow below steps to use the query:

     

    1. Click on 'fx' to add custom steps.

     

    Previous step name:

     

    2. Replace bold part with your column names and step name.

     

    =Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate]), #duration(1, 0, 0, 0)))
                 previous step name                          startdate                       enddate   startdate

     

     

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi pierre415,

     

    Click on the arrow button and choose 'expand to new row' option:

     

    Regards,

    Xiaoxin Sheng

Z7-852's avatar
Z7-852
Icon for Helper I rankHelper I
9 years ago

In order to use CALENDAR function you need to specify single values.

What you need to write is something in lines "Table 2 = CALENDAR(MIN(Table1[Start Date]), MAX(Table1[End Date]))"

 

But this gives you just one table (no duplicate dates). I think it's easier to write a M script that goes through each row in the query and adds dates between start and end date to new table with second column being the name column value.

  • pierre415's avatar
    pierre415
    Icon for Helper I rankHelper I
    9 years ago

    Thanks, I wouldn't know what an M script is but as for specifying single values, surely couldn't I use something like SUMX to reference a single row?