Hi, I am syncrhonizing data from VSTS (Azure Devops) - Analytics View. I want to create a table based on last 30 days historical table which I fetch from Analytics plugin. I was able to build an iteration paths table by simply using: Interations Dim = DISTINCT('Work Items - Last 30 days'[Iteration Path])However I would like to build a table, which consists of:Distinct name of iteration pathIteration start dateIteration end dateI tried with summarize, but the problem is that there might be multiple values.. Ideally I would like to have a table based on dates in a range.Date Range = CALENDAR(DATE(2019;01;01); DATE(2020;12;31))which includes those fields above...To be more precise, here are the data I need to transform... Current table Iteration PathIteration Start DateIteration End DateTitleDate Effort completedEffort remainingIteration 101/01/201907/01/2019Task 102/01/2019 15Iteration 101/01/201907/01/2019Task 202/01/2019 23Iteration 101/01/201907/01/2019Task 104/01/2019 52Iteration 101/01/201907/01/2019Task 204/01/2019 34Iteration 101/01/201907/01/2019Task 305/01/2019 45Iteration 208/01/201915/01/2019Task 608/01/2019 15Iteration 208/01/201915/01/2019Task 408/01/2019 23Iteration 208/01/201915/01/2019Task 808/01/2019 52Iteration 208/01/201915/01/2019Task 910/01/2019 34Iteration 208/01/201915/01/2019Task 910/01/2019 45Iteration 316/01/201923/01/2019Task 1116/01/2019 15Iteration 316/01/201923/01/2019Task 1216/01/2019 23 Target table Date Iteration Start DateIteration End Date 01/01/2019Iteration 101/01/201907/01/2019 02/01/2019Iteration 101/01/201907/01/2019 03/01/2019Iteration 101/01/201907/01/2019 04/01/2019Iteration 101/01/201907/01/2019 05/01/2019Iteration 101/01/201907/01/2019 06/01/2019Iteration 101/01/201907/01/2019 07/01/2019Iteration 101/01/201907/01/2019 08/01/2019Iteration 208/01/201915/01/2019 09/01/2019Iteration 208/01/201915/01/2019 10/01/2019Iteration 208/01/201915/01/2019 11/01/2019Iteration 208/01/201915/01/2019 12/01/2019Iteration 208/01/201915/01/2019 13/01/2019Iteration 208/01/201915/01/2019 14/01/2019Iteration 208/01/201915/01/2019 15/01/2019Iteration 208/01/201915/01/2019 16/01/2019Iteration 316/01/201923/01/2019 Any support is greatly appreciatedAll the bestMarcin

Anonymous

Not applicable

6 years ago

Solved

How to create sub table based on other table

Hi,

I am syncrhonizing data from VSTS (Azure Devops) - Analytics View.

I want to create a table based on last 30 days historical table which I fetch from Analytics plugin.

I was able to build an iteration paths table by simply using:

Interations Dim = DISTINCT('Work Items - Last 30 days'[Iteration Path])

However I would like to build a table, which consists of:

Distinct name of iteration path
Iteration start date
Iteration end date

I tried with summarize, but the problem is that there might be multiple values..

Ideally I would like to have a table based on dates in a range.

Date Range = CALENDAR(DATE(2019;01;01); DATE(2020;12;31))

which includes those fields above...

To be more precise, here are the data I need to transform...

Current table
Iteration Path	Iteration Start Date	Iteration End Date	Title	Date	Effort completed	Effort remaining
Iteration 1	01/01/2019	07/01/2019	Task 1	02/01/2019	1	5
Iteration 1	01/01/2019	07/01/2019	Task 2	02/01/2019	2	3
Iteration 1	01/01/2019	07/01/2019	Task 1	04/01/2019	5	2
Iteration 1	01/01/2019	07/01/2019	Task 2	04/01/2019	3	4
Iteration 1	01/01/2019	07/01/2019	Task 3	05/01/2019	4	5
Iteration 2	08/01/2019	15/01/2019	Task 6	08/01/2019	1	5
Iteration 2	08/01/2019	15/01/2019	Task 4	08/01/2019	2	3
Iteration 2	08/01/2019	15/01/2019	Task 8	08/01/2019	5	2
Iteration 2	08/01/2019	15/01/2019	Task 9	10/01/2019	3	4
Iteration 2	08/01/2019	15/01/2019	Task 9	10/01/2019	4	5
Iteration 3	16/01/2019	23/01/2019	Task 11	16/01/2019	1	5
Iteration 3	16/01/2019	23/01/2019	Task 12	16/01/2019	2	3

Target table
Date		Iteration Start Date	Iteration End Date
01/01/2019	Iteration 1	01/01/2019	07/01/2019
02/01/2019	Iteration 1	01/01/2019	07/01/2019
03/01/2019	Iteration 1	01/01/2019	07/01/2019
04/01/2019	Iteration 1	01/01/2019	07/01/2019
05/01/2019	Iteration 1	01/01/2019	07/01/2019
06/01/2019	Iteration 1	01/01/2019	07/01/2019
07/01/2019	Iteration 1	01/01/2019	07/01/2019
08/01/2019	Iteration 2	08/01/2019	15/01/2019
09/01/2019	Iteration 2	08/01/2019	15/01/2019
10/01/2019	Iteration 2	08/01/2019	15/01/2019
11/01/2019	Iteration 2	08/01/2019	15/01/2019
12/01/2019	Iteration 2	08/01/2019	15/01/2019
13/01/2019	Iteration 2	08/01/2019	15/01/2019
14/01/2019	Iteration 2	08/01/2019	15/01/2019
15/01/2019	Iteration 2	08/01/2019	15/01/2019
16/01/2019	Iteration 3	16/01/2019	23/01/2019

Any support is greatly appreciated

All the best

Marcin

Need Help

Show and Tell

Anonymous

6 years ago

Hi Anonymous ,

You can try to use following calculate table formula to create a expand table with all detail date records:

New Table =
VAR _calendar =
    CALENDAR (
        MIN ( Table[Iteration Start Date] ),
        MAX ( Table[Iteration End Date] )
    )
RETURN
    FILTER (
        CROSSJOIN (
            SUMMARIZE ( Table, [Path], [Iteration Start Date], [Iteration End Date] ),
            _calendar
        ),
        [Date] >= [Iteration Start Date]
            && [Date] <= [Iteration End Date]
    )

Regards,

Xiaoxin Sheng

Forum Discussion

How to create sub table based on other table

3 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Create a new table based on filters

How to create hierarchy not in fct table or based on integer key columns?