Create a new table and merge data based on specific 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  CLICK 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.

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

[Irwan]

@InsightSeeker 

not really clear on what is your requirements.

1. what do you mean by first, second, third, fourth leg_num? is it refer to number 1,2,3,4? and what is happens to 5,6,7,8 as you have those values.

2. what is result difference between first,second,third,fourth leg_num in Condition 1 and Condition 2? basically it was just copied-pasted.

3. in Condition 1, why there are some rows with repeated value?

4. how to get Condition 3 result? the picture you shown is not very clear explaining on how to get that value.

5. your 'Co2' column does have no value. so how you can get that sum value?

Please give a clear explaination on what your result so people here can help you better.

Thank you.

[InsightSeeker]

Hi @Irwan  -  Please find below my comments:-

1. what do you mean by first, second, third, fourth leg_num? is it refer to number 1,2,3,4? and what is happens to 5,6,7,8 as you have those values. Yes, Leg_Num refers to the 1,2,3,4,5,6,7,8 etc... In this oder the from & to coloumns should be joined.

2. what is result difference between first,second,third,fourth leg_num in Condition 1 and Condition 2? basically it was just copied-pasted. Condition one is joinging of sectors wheareas Condition 2 is joining of dates.

3. in Condition 1, why there are some rows with repeated value? These repeated values in Condition 1 is because it had different unique ID and Number.

4. how to get Condition 3 result? the picture you shown is not very clear explaining on how to get that value. Condition 3 is joining of the category column.

5. your 'Co2' column does have no value. so how you can get that sum value? Some the cells will not have any value, in this case 0 is to be returned.

Require :- 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.