Need help in creating new table and merging data based of Values

[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 here.

**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.

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

@lbendlin 

[Anonymous · Accepted solution]

Hi @InsightSeeker 

Based on your description, you can create a new calculated table and input the following code.

Table =
SUMMARIZE (
    ADDCOLUMNS (
        Seg,
        "Column1",
            VAR __max_leg_num =
                CALCULATE ( MAX ( Seg[Leg_Num] ), ALLEXCEPT ( Seg, Seg[unique_id] ) )
            VAR __last_to =
                CALCULATE (
                    MAX ( Seg[To] ),
                    ALLEXCEPT ( Seg, Seg[unique_id] ),
                    Seg[Leg_Num] = __max_leg_num
                )
            RETURN
                CONCATENATEX (
                    TOPN (
                        __max_leg_num,
                        FILTER ( Seg, [unique_id] = EARLIER ( Seg[unique_id] ) ),
                        [Leg_Num]
                    ),
                    [From],
                    "-"
                ) & "-" & __last_to,
        "Column2",
            VAR __max_leg_num =
                CALCULATE ( MAX ( Seg[Leg_Num] ), ALLEXCEPT ( Seg, Seg[unique_id] ) )
            VAR __last_todate =
                CALCULATE (
                    MAX ( Seg[To_Date] ),
                    ALLEXCEPT ( Seg, Seg[unique_id] ),
                    Seg[Leg_Num] = __max_leg_num
                )
            VAR __last_fromdate =
                CALCULATE (
                    MAX ( Seg[From_Date] ),
                    ALLEXCEPT ( Seg, Seg[unique_id] ),
                    Seg[Leg_Num] = __max_leg_num
                )
            VAR _retultdate =
                IF ( __last_fromdate <> __last_todate, "|" & __last_todate )
            RETURN
                CONCATENATEX (
                    SUMMARIZE (
                        TOPN (
                            __max_leg_num,
                            FILTER ( Seg, [unique_id] = EARLIER ( Seg[unique_id] ) ),
                            [Leg_Num]
                        ),
                        [From_Date]
                    ),
                    [From_Date],
                    "|"
                ) & _retultdate,
        "Column3",
            VAR __max_leg_num =
                CALCULATE ( MAX ( Seg[Leg_Num] ), ALLEXCEPT ( Seg, Seg[unique_id] ) )
            RETURN
                CONCATENATEX (
                    TOPN (
                        __max_leg_num,
                        FILTER ( Seg, [unique_id] = EARLIER ( [unique_id] ) ),
                        [Leg_Num]
                    ),
                    [Category],
                    "-"
                ),
        "Column4", CALCULATE ( SUM ( Seg[Co2] ), ALLEXCEPT ( Seg, Seg[unique_id] ) ),
        "Column5", CALCULATE ( SUM ( Seg[KM] ), ALLEXCEPT ( Seg, Seg[unique_id] ) )
    ),
    Seg[unique_id],
    [Number],
    [Column1],
    [Column2],
    [Column3],
    [Column4],
    [Column5]
)

Output

Best Regards!

Yolo Zhu

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

[InsightSeeker]

Hi @Anonymous  - The suggestion you provided is working as required, but I am encountering issues in a few cases when using this DAX in our database.

When the same logic is applied with the BI database connected to Excel, it works perfectly. However, when it comes to the SQL database, the same transactions do not work.

Is there anything that might be causing this issue?