Dear PowerBI community,
I've got a problem merging 2 requests.
On the first one, I've got columns like bill_id and bill_line_total.
Of course, this does not reflect the exact reality; It is just for the example.
The bill_id and bill_line_total can appear on multiple lines but bill_id + bill_line_total is unique. (I know, having a bill_line_id would have been better, but I don't have one).
| bill_id | bill_line_total | other columns
-------------------------------------------
| 0 | 4.2 |
| 0 | 1.3 |
| 0 | 2.5 |
| 1 | 0.2 |
| 1 | 1.4 |
| 2 | 0.2 |
On the second request, I got duplicate entries because the bill_line_total is always 0 in the data source. So every lines of the same bill_id are duplicated.
| bill_id | bill_line_total | other columns
--------------------------------------------
| 0 | 0.0 |
| 0 | 0.0 |
| 0 | 0.0 |
| 1 | 0.0 |
| 1 | 0.0 |
| 2 | 0.0 |
Other columns of the 2 requests are not the same, that's why I need to merge them. The order of lines in a bill_id is not important. I can merge line 1 of bill_id_1 with line_2 of bill_id_1 of the other request.
I need to merge the 2 requests but I can't because I don't have any id in common.
Of course, I cannot change the source of data, so, I can think only of this solution:
- I create a auto-increment id for each duplicated bill line and restart it to 0 for each new bill ? For instance:
bill_id_0-bill_line_0
bill_id_0-bill_line_1
bill_id_0-bill_line_3
bill_id_1-bill_line_0
bill_id_1-bill_line_1
bill_id_2-bill_line_0
So, could you help me to know if there is another solution, and if not, how can I do to create an auto-increment id which restart to 0 on each new bill_id ?
Best regards and thank you !
