Generate a Table by Merging Data Based on Unique Identifiers and Conditions*

[InsightSeeker]

Hello everyone,

I require your assistance in consolidating data from multiple columns into a single table based on specific conditions. Instead of generating a calculated column, I intend to create a new table.

I have provided a PBIX sample data file for reference. You can access it  CLICK HERE

Note- eg:- Each unique ID (or multiple unique IDs) will have multiple "From" and "To" data points. I need to join this data in ascending order based on the `Leg_num`. Once all the data is joined, I need to create a new table that displays each unique ID & Number only once, along with all the associated conditions in separate columns.

**Condition 1 (Column1):**

For each unique_id:

- Find the first Leg_Num and merge the corresponding From and To values into one column.

- Find the second Leg_Num and merge the corresponding From and To values into one column.

- Find the third Leg_Num and merge the corresponding From and To values into one column.

- Find the fourth Leg_Num and merge the corresponding From and To values into one column.

Continue this process...

Use "-" between From and To in the final result.

**Condition 2 (Column2):**

For each unique_id:

- Find the first Leg_Num and merge the corresponding From_Date and To_Date into one column.

- Find the second Leg_Num and merge the corresponding From_Date and To_Date into one column.

- Find the third Leg_Num and merge the corresponding From_Date and To_Date into one column.

- Find the fourth Leg_Num and merge the corresponding From_Date and To_Date into one column.

Continue this process...

Use "|" between From and To in the final result.

**Condition 3 (Column3):**

For each unique_id:

- Find the first Leg_Num and merge the Category with the second Leg_Num.

Continue this process...

Use "-" between From and To in the final result.

**Condition 4 (Column4):**

For each unique_id:

- Sum all Co2 values.

**Condition 5 (Column5):**

For each unique_id:

- Sum all KM values.

Here is the link to the report Client Here

The resulting table should be displayed as follows:

unique_id	Number	**Condition 1 (Column1):**	**Condition 2 (Column2):**	**Condition 3 (Column3):**	**Condition 4 (Column4):**	**Condition 5 (Column5):**
1095815	6073252842	KGL-NBO	05-Apr-24	H	31312	471
1095828	6073252847	DXB-KWI-DXB	04-Apr-24|05-Apr-24	K-K	69011	1062
1095879	6073199119	CAI-FCO-CAI	12-Apr-24|17-Apr-24	Z-Z	77527	2672
1095880	6073199120	CAI-FCO-CAI	12-Apr-24|17-Apr-24	Z-Z	77527	2672
1095884	6073256049	NBO-KGL	03-Apr-24	Q	46215	471
1095932	6073252871	DXB-LHR-DXB	29-Apr-24|04-May-24	O-O	98817	6842
1095936	6073252873	CAI-DXB-CAI	25-Apr-24|06-May-24	O-O	107333	3006

[v-heq-msft · Accepted solution]

Hi @InsightSeeker ,
Create calculate columns

Column 1 = 
VAR FromToList = 
    CONCATENATEX(
        FILTER(
            Seg, 
            Seg[unique_id] = EARLIER(Seg[unique_id])
        ), 
        Seg[From] & "-" & Seg[To], 
        "-", 
        Seg[Leg_Num], 
        ASC
    )
VAR SplitValues = SUBSTITUTE(FromToList, "-", "|")
VAR ValuesList = PATHITEM(SplitValues, 1, TEXT)
VAR Result = 
    CONCATENATEX(
        FILTER(
            GENERATESERIES(1, PATHLENGTH(SplitValues)),
            PATHITEM(SplitValues, [Value], TEXT) <> PATHITEM(SplitValues, [Value] + 1, TEXT)
        ),
        PATHITEM(SplitValues, [Value], TEXT),
        "-"
    )
RETURN Result

Column 2 = 
VAR From_Date = 
CALCULATE(
    MAX(Seg[From_Date]),
    ALLEXCEPT(
        Seg,
        Seg[unique_id]
    )
)
VAR To_Date = 
CALCULATE(
    MAX(Seg[To_Date]),
    ALLEXCEPT(
        Seg,
        Seg[unique_id]
    )
)
VAR Result1 = FORMAT(From_Date,"dd-mmm-yyyy")
VAR Result2 = FORMAT(To_Date,"dd-mmm-yyyy")
RETURN
IF(
    Result1 <> Result2,
    Result1 & "|" & Result2,
    Result1
)

Column 3 = 
CONCATENATEX(
    FILTER(
        Seg, 
        Seg[unique_id] = EARLIER(Seg[unique_id])
    ), 
    Seg[Category], 
    "-", 
    Seg[Leg_Num], 
    ASC
)

Column 5 = 
CALCULATE(
    AVERAGE(Seg[KM]),
    ALLEXCEPT(
        Seg,
        Seg[unique_id]
    )
)

Final output

Because Co2 has no value in the data you provided, colum4 is not calculated, and if you want it to be calculated, you can just do the aggregation.Column 5's representation in the table needs to be realized through aggregation

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[InsightSeeker]

Hi @v-heq-msft  - I am able to get the result by following your suggestion. I only need one change with the condition 2 to be as below. Can you please suggest on how i can achive this condition?

**Condition 2 (Column2):**

For each unique_id:

- Find the first Leg_Num and merge the corresponding From_Date  and

- Find the second Leg_Num and merge the corresponding From_Date  and

- Find the third Leg_Num and merge the corresponding From_Date  and

- Find the fourth Leg_Num and merge the corresponding From_Date and

Continue this process...

Use "|" between From and To in the final result.

[v-heq-msft]

Hi @InsightSeeker ,
You can try this

Column = 
VAR _time = FORMAT(Seg[From_Date],"dd-mmm-yyyy")
RETURN
CONCATENATEX(
    FILTER(
        Seg, 
        Seg[unique_id] = EARLIER(Seg[unique_id])
    ), 
    _time, 
    "|", 
    Seg[Leg_Num], 
    ASC
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly