cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rramillien Frequent Visitor
Frequent Visitor

Add ID to duplicated entries

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 !

2 REPLIES 2
Community Support Team
Community Support Team

Re: Add ID to duplicated entries

Hi @rramillien

To create an auto-increment id which restart to 0 on each new bill_id, after inporting data into power bi desktop, go to "Edit queries", select "Add column"->"Add an index column"(from 0)

 

then select "Home"->"merge queries", based on the new created id column.

 

If you have any problem, please let me know.

 

Best Regards

Maggie 

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
rramillien Frequent Visitor
Frequent Visitor

Re: Add ID to duplicated entries

Hello, thank you for your response.

I'm not sure to fully understand it.

 

If I create an index column, it starts at 0 but do no restart on each new bill_id.

I got something like this:

| bill_id | bill_line_total   | line_index

-----------------------------------

| 0         | 4.2                   | 0

| 0         | 1.3                   | 1

| 0         | 2.5                   | 2

| 1         | 0.2                   | 3

| 1         | 1.4                   | 4

| 2         | 0.2                   | 5

 

I would have something like this

 

| bill_id | bill_line_total   | line_index

-------------------------------------------

| 0         | 4.2                   | 0

| 0         | 1.3                   | 1

| 0         | 2.5                   | 2

| 1         | 0.2                   | 0

| 1         | 1.4                   | 1

| 2         | 0.2                   | 0

 

Best regards,