Today was my first time trying out PowerQuery and PowerBI. I web-scrapped data from Rotten Tomatoes listing of their Top 100 Netflix Movies, as you can imagine taking data straight from a webpage means a lot of cleaning!
A small nuance to pay attention to when merging is whether to do this through the 'Add' Tab or the 'Transform' Tab. The logic for each of these tabs is that Add will create a whole new column whereas Transform changes the existing two columns.
For context, there were two columns, one for the director and another for a second director. As films varied in the number of directors I wanted to merge these into one column to say Director(s)
What I noticed is when I used Transform>Merge Columns by a delimiter such as a comma, this would combine null fields with a comma too. As an example, the first row 'Cornell John' would become 'Cornell John,'. However, when I use Add>Merge Columns this would not add the null fields so extra commas were not seen in rows that had only one director.
So to save having to do a cleaning step to remove extra delimiters when merging, use Add>Merge Columns in PowerQuery.
That is my quick query tip, hope that helps anyone else who stumbles across the same issue!