Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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

@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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.