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

     

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