![]() ![]() Since our task is to compare overlapping periods for the same resource, we would need to keep in the table only rows in which the task in Task column is not equal to the task in Source2.Task and the resource in Resource column is equal to the resource in Source2.Resource. Filtering Different Tasks of the Same Resource Change the column types of these columns to Date/Time. ![]() Scrolling right in the table, you will notice that the types of columns Source2.Start and Source2.End are not set (You can see that the column headers have the ABC123 icon). So, in total we have 25 rows instead of the original 5 as we apply the cartesian product.īefore we keep only the pairs that we would like to match, let’s make sure that we have the correct column types. You can see in the preview pane that Task 1 was duplicated five times, and is now paired against Task 1, Task 2, Task 3, Task 4 and Task 5 which are shown in the Source2.Task column. Check Use original column name as prefix and click OK. In the expand pane, make sure that all the columns are selected. Click the expand column control on the right side of Source2 column header. Now, when we have the table objects in Source2, it is time to expand these tables. But let’s wait with it for now to keep the solution simple and clear. To solve this issue, we will use Table.Buffer. Note: This technique may have slow performance if you load the original source table form an external data source. In the Custom Column dialog box, enter Source2 as New column name, enter the formula: = Source Then, we’ll expand the table objects in the new column and generate a table with all the permutations of pairs. To compare between each row in the Overlapping Tasks table, we will create a new column that will include the table itself as an object in each row of the original table. By matching each task against all the other tasks in the table, we will be able to find the overlapping time periods. The Cartesian Product enables you to combine two datasets in order to apply a calculation or filtering on each combination of the paired records. Now we are going to use a Cartesian Product technique, which I describe in details in Chapter 11 of my book. While Power Query Editor is open, right click on the Tasks query in Queries pane and select Reference in the shortcut menu. #"Changed Type" = Table.TransformColumnTypes(Source,) Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRCkotzi8tSk4Fc8z1jfSNDAwtFSytDAyASMHRF1nUFCoa4KsUqwM1wwjZDCNk1YaGWA2xxGKIMU5DjBCqkUQtsJhhgmyGMbJqE6xmwJ2HbIgpTkMMsRpijmRGLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta ) in type table ), To do it you can copy and paste the code below into a blank query in Power Query: let Import the table above and name the query Tasks. Can you automate the detection of overlaps in Power BI or Excel? In this blog post we’ll describe how to do it. While it is easy to visually detect the overlaps in a Gantt chart on a small dataset, it is much more interesting to assume that the number of tasks and resources can be significant high. ![]() The figure above highlights the overlapping time periods. In the sample data below, you can find 5 tasks allocated to 3 resources. ![]() Can you find the overlapping time periods and the over-allocated resources? Your job is to find the over-allocated resources that are assigned to multiple tasks with overlapping time periods (as demonstrated in the figure below). You have a list of resources that are assigned to tasks. Can you find all the overlapping time periods in the table? To make this challenge more specific, imagine you are a project manager responsible for tracking the progress of a mission-critical project in your organization. Your input data is a table with start and end dates (including time). In today’s post, we will go through the solution in details. Follow my last blog post here, I introduced the overlapping time periods challenge and shared the M queries for the solution. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |