Forum Discussion
Helper IIICreate a new table and merge data based on specific values.
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 |
2 Replies
IrwanSuper User
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.
InsightSeekerHi 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.