Merge only differences in the rows in a power query

[kmilarov]

Folks, I have a query with a column TagID that I use as a unique value. And many additional columns with different values. 
but , for some rows I have multiple different values in the same column that results to duplicate/ multiply the unique TagID. See simplified example here. 
In order to keep single , unique TagID column, I want to compare all rows with the same TagID (it could be only one, or multiple rows) and to check the content of every column in these rows - and if the data is absolutely the same , do not merge and keep the first row column data. If the content is different - to mere with the previous row's column data separating the content by delimiter for example: ///

And at the end to have only one row per TagID with all information.  See example of the end result what i expect. 

preferably this transformation should be done in the power query 

thanks

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiwuYBtTKyZ32Vc43?e=djYjTj

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiwuYBtTKyZ32Vc43?e=djYjTj

[kmilarov]

@Ahmedx  Hi, I mentioned that the formula merges only the first two rows. But sometimes we have 3-4-5 rows with the same TagID. Can I emend the formula adding column3, 4 etc? or somehow to calculate the number of the required columns first and to merge them based on this? Irevised the example excel file adding third row with TagID 12 for example

= (x)=> [
t1 = Table.Transpose(Table.DemoteHeaders(x)),
t2 = Table.AddColumn(t1, "Custom", each if [Column1] = "Element:Text" then [Column2] else if [Column2]=[Column3] then [Column2] else Text.From( [Column2]) & " /// " & Text.From( [Column3]))[[Column1],[Custom]],
Results =Table.PromoteHeaders( Table.Transpose(t2), [PromoteAllScalars=true])][Results]

[Ahmedx · Accepted solution]

pls try another variant

https://1drv.ms/u/s!AiUZ0Ws7G26RixAyLnVFmjEhp-SZ?e=wrBTh7

[kmilarov]

Thank you! varian 2 works fine. (vsriant 3 somehow merges all values - even the one that are the same). But will test variant 2 🙂 

[Ahmedx]

yes, in option 3 I did this on purpose because I didn’t know exactly what you needed

[kmilarov]

Hi Ahmedx , while testing my real dataset with your solutin , I found some issue - when I have many rows with the same TagID, the current soution (your variant 2) retruns only 3 rows/entries after combining (with /// separator). Is there an option in your variant 2 to have unlimited combined rows and since some columns may have different number of distinguished rows, can we have  a "-" on the column/row when there is no differences? THANKS

[Ahmedx]

please share the data so I can understand the problem

[kmilarov]

Hi Ahmedx, I put on hold my question/ request for few days while trying to extract sample data (the real dataset is huge sql xml dataset that I cannot share ). I will come back in couple of days. 

[Ahmedx]

or this variant

https://1drv.ms/u/s!AiUZ0Ws7G26RixFMPtxnSdGw5stR?e=e6Z7zf

[kmilarov]

Thank you, Ahmedx. I am trying to implement the formula/steps with the real complex file. Could you advise at what step did you create and insert the trans formula ? I am entering it within the table , but When creating it - I got and error:

An error occurred in the ‘Table’ query. Expression.Error: We cannot convert a value of type Function to type Table.
Details:
Value=[Function]
Type=[Type]

[kmilarov]

disregard this. I managed to fix it as per your pbix file. It works now very well. Thank you!