Forum Discussion
Merging Data from two Columns but Maintaining All Row Information
I wish to create visuals using data from sample table below but view across time by ID (column A) whilst retaining all unique values. Problem is ID ofetn repeats and BI combines values each time it see the ID repeated. I want to split this out so that BI is using the unique-ID at column W but shows the ID at column A when displaying visuals,
I think I need to create a new column in Power Querry that reatins unique values with each row even when ID is duplicated.
For example what I am seeing is picture 1 below, each time say AB 781 is duplicaed those values are combines to create the total of all AB781's (approx 0.3M)
What I would like to see is the second picture, but with the ID's displayed on the X axis instead of Unique IDs shown in Pic 2.
Below is a table of smaple data that hopefully helps and I thank anyone on advance for offering advice in what to many is probably very simple but the answer is eluding me.
| ID | origin | return_id | planned_depart_origin | planned_arrive_loadpoint | loss_reason | comments | loadpoint | contract_id | planned_start_load | unit | planned_depart_loadpoint | planned_start_unload | planned_finish_unload | status | planned_finish_load | loss_respparty | locked | unloader | email_sent | planned_arrive_unloader | haulage_provider | unique_id | logdatetime | wagons_not_loaded | wagons_loaded | wagons_loaded_capacity | nrt_start_first_unload | nrt_finish_first_unload | first_unload_tonnes | sampler_bypassed | split_unload | pay_weigher_bypassed | nrt_start_second_unload | nrt_finish_second_unload | second_unload_tonnes | start_first_unload | finish_first_unload | start_second_unload | finish_second_unload | unload_delay | unload_time | split_unload_time | departure_delay | pass_unloader | arrive_loadpoint | start_loading | finish_loading | depart_loadpoint | arrive_unloader | provisioning_required | start_provisioning | finish_provisioning | cycle_time |
| AB781 | NG | AT782 | 1/07/2019 11:20 | 1/07/2019 15:46 | AB | 1/07/2019 16:01 | 70 | 1/07/2019 19:49 | 1/07/2019 23:00 | 2/07/2019 1:50 | Completed | 1/07/2019 19:01 | 1 | NT | 1 | RHP | 639075 | ######## | 88 | 8619.6 | 7510638 | 7519109 | 8471 | 2/07/2019 1:18 | 2/07/2019 6:38 | 2.3 | 5.33 | 1.67 | 1/07/2019 13:00 | 1/07/2019 15:49 | 1/07/2019 16:05 | 1/07/2019 19:55 | 1/07/2019 21:04 | 1/07/2019 22:31 | 17.63 | ||||||||||||||||||
| AB781 | AT | AT782 | 8/07/2019 8:23 | 8/07/2019 8:50 | AB | 8/07/2019 9:05 | 64 | 8/07/2019 13:30 | 8/07/2019 18:44 | 8/07/2019 22:08 | Completed | 8/07/2019 12:05 | 1 | NT | 1 | RHP | 639852 | ######## | 88 | 8628.4 | 7757893 | 7766375 | 8482 | 8/07/2019 20:48 | 9/07/2019 0:16 | 2.066667 | 3.47 | 2.12 | 8/07/2019 10:30 | 8/07/2019 11:20 | 8/07/2019 11:27 | 8/07/2019 14:34 | 8/07/2019 15:24 | 8/07/2019 15:58 | 1 | 8/07/2019 20:00 | 8/07/2019 20:30 | 13.77 | |||||||||||||||
| AB781 | AT | AT782 | 17/07/2019 5:23 | 17/07/2019 5:50 | AB | 17/07/2019 6:05 | 64 | 17/07/2019 9:29 | 17/07/2019 11:57 | 17/07/2019 15:21 | Completed | 17/07/2019 9:05 | 1 | NT | 1 | RHP | 640816 | ######## | 88 | 8626.2 | 7995847 | 8004499 | 8652 | 17/07/2019 12:07 | 17/07/2019 15:03 | 0.166667 | 2.93 | 17/07/2019 5:18 | 17/07/2019 6:10 | 17/07/2019 7:27 | 17/07/2019 9:57 | 17/07/2019 10:13 | 17/07/2019 11:42 | 9.75 | |||||||||||||||||||
| AB781 | NG | AT782 | 23/07/2019 11:00 | 23/07/2019 16:30 | AB | 23/07/2019 16:45 | 75 | 23/07/2019 21:05 | 23/07/2019 23:03 | 24/07/2019 2:27 | Completed | 23/07/2019 19:45 | 1 | NT | 1 | RHP | 641550 | ######## | 88 | 8630.6 | 8225916 | 8234583 | 8667 | 23/07/2019 23:13 | 24/07/2019 2:02 | 0.166667 | 2.82 | 4.92 | 23/07/2019 15:55 | 23/07/2019 18:57 | 23/07/2019 19:10 | 23/07/2019 21:34 | 23/07/2019 21:55 | 23/07/2019 22:45 | 1 | 23/07/2019 22:50 | 23/07/2019 23:10 | 10.12 |
2 Replies
Hi Buster,
For graph charts, they are used in analysis and aggregate record values based on current chart axis, category and legend. You can't cancel the summary effect and show detailed records on these charts.
For your requirement, I'd like to suggest you use table visual to instead, it can work as a common tabular to list detail records without aggregate.Regards,
Xiaoxin Sheng
