Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
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"
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.
Treb Gatte | Business Solutions MVP | @tgatte | Blog | Power BI Recordings
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |