Log In
Register
Home
Active Member
September 22, 2020
Offline
Hello first time trying a post.
Trying to concatenate multiple columns in a Power Query.
I have a SQL 2014 table with the following Columns
A1 = LastName, B2= Given1, C2= Given2, D2= Home phone, E2 =Mobile phone
If I go to an excel table and use the =Concatenate(A1,", ",B2,", ",C2," - Home:",D2," - Mobile:",E2)
I get the results I expect to see McLean, Scotty - Home:905-664-1682 - Mobile: 905-516-3189
But I have some data that may be missing one of the phone numbers or even a Given2 are Blank in the table
With the Concatenate function I will still see results with Blank Cells
McLean, Scotty, Ian - Home:905-664-1682 - Mobile: - So mobile number is Blank
OR
McLean, Scotty, Ian - Home: - Mobile: 905-516-3189 - So Home number Is Blank
Or
McLean, Scotty, - Home: - Mobile: 905-516-3189 - So Given2 is Blank
In Power Query I can not get it to work with Blank Cells
in Power Query is if I use this method
=[fd_LastName]&" "&[fd_Given1]&", "&[fd_Given2]&" -Mobile: "&[fd_MobilePhone]&"- Home: "&[fd_HomePhone]
if all the fields Cells have data I get the results I expect which is great
but if there are blank Cells all i see in the Cell is the word with Brackets (blank)
If I use the ConcatenateX( ) Seen this on a previous post
I get this error
Expression.Error: The name 'ConcantenateX' wasn't recognized. Make sure it's spelled correctly.
not sure what I'm doing wrong but any help would be great
October 5, 2010
Offline
Hi Scotty,
If you Merge Columns in the PQ Editor you can do this.
Click on the 2 column headers that you want to merge, then right click one of the headers -> Merge Columns.
Choose Custom Separator, and enter the separator you want.
Click OK to merge, then repeat.
See attached file that uses these steps.
If you need further assistance please attach a file with data. You can always load some of the data from SQL into an Excel tale and that can be used as the source for the query.
Regards
Phil
Active Member
September 22, 2020
Offline
Thanks Phil
The issue is I still need to keep the original columns sperate for other Queries I run against the same data set. If I use the merge Columns function it removes the Original Columns.
As I'm actually looking at several tables from the same SQL data source I was trying to save space and time without replicating the data in Excel tables and setting up all the relations ships twice. I need to be able to Query the data real time.
and yes if I do it in Excel with Concatenate if works perfectly fine - I get the results I require even with Blank Cells.
Power Query is new to me I normally do thinks in Access for SQL. But in this particular case with he dataset I'm using Power Query, Pivot tables and slicers is a very quick way to give the end user extremely powerful Dashboards with No coding.
I'm trying to produce a contact listing for personnel using a pivot table with Slicers. I really works very well in this Case with the exception of there is blank data (Cells) in the table.
I just thought with Concatenate works so well in a Table there must be a work around to use it in Power Query
Thanks Scotty
July 16, 2010
Offline
Hi Scotty,
If you want to retain the original columns after merging, go to the Add Column tab and Merge from there.
Mynda
Active Member
September 22, 2020
Offline
Phil and Mynda
Thanks for your advice. Right after replying to Phil's original note. I started looking at the Merge function a little more. And yes if I had the Add column select I was able was merge the columns required. And now it is working fine even with the Blank Cells. I just had to create a few extra columns to get the formatting I wanted to have. But once it is set up it works great.
Again thanks for your advice really appreciated.
Stay well and Safe
Scotty McLean
1 Guest(s)
