Generate days view by start and end date?

[wll6568]

Say if I have a start and end date columns from a Sharepoint list, is there a way to generate a daily view report from them?

For example:

Assignment list:

- Start Date

- End Date

- Project Name

I want to have a report like this:

Mon 18/9/2016        Tues 19/9/2016       Wed 20/9/2016     Thurs 21/9/2016     Friday 22/9/2016

Project A                      Project C                                                  Project D

Project B

[Eric_Zhang · Accepted solution]

@wll6568

Check the below sample.

let
  Source = Table.FromRecords({    
  [Project Name= "Project A", Start Date= "2016-09-01", End Date = "2016-09-04"],    
  [Project Name= "Project B", Start Date= "2016-09-02", End Date = "2016-09-05"],  
  [Project Name= "Project C", Start Date= "2016-09-03", End Date = "2016-09-06"]
  }),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "JoinCol", each 1),
    Source2 = Table.FromList(List.Dates(#date(2016, 1, 1), 365, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"date"}, null, ExtraValues.Error),
    Table2 = Table.AddColumn(Source2, "JoinCol", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"JoinCol"},Table2,{"JoinCol"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"date"}, {"NewColumn.date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"JoinCol"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [NewColumn.date] >= [Start Date] and [NewColumn.date] <= [End Date]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Project Name", "Project Name - Copy"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column", {{"NewColumn.date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column", {{"NewColumn.date", type text}}, "en-US")[NewColumn.date]), "NewColumn.date", "Project Name"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Start Date", "End Date", "Project Name - Copy"})
in
    #"Removed Columns1"

[trebgatte]

This article may provide some ideas as you would have to generate a detail table by day for each task, similar to how Project Online generates the same sort of data table.

http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...

Treb Gatte | Business Solutions MVP | @tgatte | Blog | Power BI Recordings