cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wll6568
New Member

Generate days view by start and end date?

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

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

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

 

Capture.PNG

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft
Microsoft

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

 

Capture.PNG

View solution in original post

trebgatte
MVP

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.