Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
2 years ago
Solved

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

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_idNumber**Condition 1 (Column1):****Condition 2 (Column2):****Condition 3 (Column3):****Condition 4 (Column4):****Condition 5 (Column5):**
10958156073252842KGL-NBO05-Apr-24H31312471
10958286073252847DXB-KWI-DXB04-Apr-24|05-Apr-24K-K690111062
10958796073199119CAI-FCO-CAI12-Apr-24|17-Apr-24Z-Z775272672
10958806073199120CAI-FCO-CAI12-Apr-24|17-Apr-24Z-Z775272672
10958846073256049NBO-KGL03-Apr-24Q46215471
10959326073252871DXB-LHR-DXB29-Apr-24|04-May-24O-O988176842
10959366073252873CAI-DXB-CAI25-Apr-24|06-May-24O-O1073333006

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      InsightSeeker
      Helper III
      2 years ago

      Hi Anonymous  - 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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