August 3, 2020
I’m planning to sign up for the PQ (and other) courses soon. Until I get up to speed, I wonder if I could have some pointers, please.
I want to use PQ to concatenate a number of columns, some of which have null values, with a comma to separate each value. If I simply merge the columns I end up with unwanted commas where the null values, which are always in the later column(s), were. I’d like to be able to use PQ to either (a) only concatenate non-null cells or (b) strip out trailing commas. As a bonus, I’d like to show only unique values, as shown in the example.
The background is that I’m working with a group of clubs that have different meeting days and have been given a meeting date formula for each club in the format: “1st Thu. 11; 3rd Thu. 5; 2nd Tues. 3; 4th Wed. 1” to indicate “first Thursday in November; Third Thursday in May; 2nd Tuesday in March; 4th Wednesday in January”. I’d like to transform this to a single cell showing just (ideally) the unique meeting days for each club, separated by a comma. So far, I’ve got as far as splitting the column by “;” and then using Text.BetweenDelimiters to strip out everything from each resultant column except for the Day. The challenge is that the clubs have different numbers of meetings, so I end up with some null values. If I use text.combine with a delimiter I end up with unwanted commas from the null cells.
I’m sure there are smarter ways of doing this but I’m still very new to PQ, so I’d like just to address my two options above. I’d be very grateful for any suggestions!
October 5, 2010
August 3, 2020