Forum Discussion
Helper IIMerge only differences in the rows in a power query
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
pls try another variant
Helper IIAhmedx 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]
Super Userpls try another variant
- Ahmedx
or this variant
- 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