Forum Discussion
Removing duplicate and sorting in a keywords column
Hi Anonymous ,
After sorting, you can merge rows as follows.
1. Select "Group By", then name the new column "Rows", group the keywords column by Index column and sum.
2. You can see that the aggregated column returns error value.
3. At this point, select "Advanced Editor", make the changes shown in the figure below. Which function List.RemoveItems retains the other columns except the keywords column. And the rows of the keywords column are merged group by these columns. For merging rows, you can refer to the video https://www.youtube.com/watch?v=ODLTky9z5LE .List.RemoveItems(Table.ColumnNames ( #"Sort Rows"), {"keywords column"})4. Finally, you can choose whether to delete the Index column or not.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-kkf-msft Thanks for sharing the script. I tried to recreate, but unable to for the Grouped Rows. Can you let me know how you arrived at that?
#"Grouped Rows" = Table.Group(#"Sort Rows", List.RemoveItems(Table.ColumnNames ( #"Sort Rows"), {"keywords column"}), {{"Rows", each Text.Combine([keywords column]," "), type nullable text}}),
Community SupportHi Anonymous ,
After sorting, you can merge rows as follows.
1. Select "Group By", then name the new column "Rows", group the keywords column by Index column and sum.
2. You can see that the aggregated column returns error value.
3. At this point, select "Advanced Editor", make the changes shown in the figure below. Which function List.RemoveItems retains the other columns except the keywords column. And the rows of the keywords column are merged group by these columns. For merging rows, you can refer to the video https://www.youtube.com/watch?v=ODLTky9z5LE .
List.RemoveItems(Table.ColumnNames ( #"Sort Rows"), {"keywords column"})
4. Finally, you can choose whether to delete the Index column or not.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your help. For some reason, when I did text.combine, it did not merge it alphabetically. So I added List.Sort and it worked fine. Appreciate your help.
= Table.Group(#"Removed Columns", List.RemoveItems(Table.ColumnNames (#"Removed Columns"), {"Value"}), {{"SortedKeywords", each Text.Combine(List.Sort([Value]), " "), type nullable text}})