MrExcel Publishing
Your One Stop for Excel Tips & Solutions

TEXTJOIN in Power Query


October 03, 2017 - by Bill Jelen

TEXTJOIN in Power Query

CONCATENATEX in Power Query. The new TEXTJOIN function is awesome. Can you do the same thing with Power Query? Yes. Now you can.

Watch Video

  • A viewer downloads data from a system where each item is separated by Alt+Enter
  • Bill: Why are you doing this? Viewer: It is how I inherit the data. I want to keep it that way.
  • Bill: What do you want to do with the 40% of values not in the table? Viewer: No answer
  • Bill: There is a complicated way to solve this if you have the latest Power Query tools.
  • Instead, a VBA Macro to solve it - the macro should work all the way back to Excel 2007
  • Instead of doing VLOOKUP, do a series of Find & Replace with VBA

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 21 51 I really don't know what to call
  • this one if I'm trying to attract the
  • people who use Dax I would say
  • concatenate X and power query or just
  • people use regular Excel but office 365
  • it's a text join in power query or to be
  • completely honest it's a super complex
  • set of steps in power query to enable a
  • super insane solution in Excel hey
  • welcome back to MrExcel that cast I'm
  • well yesterday in episode 2150 I
  • described the problem someone send in
  • this file where their system is
  • downloading the items on an order with
  • line feeds between them in other words
  • alt enter and see wrapped taxes turn on
  • and they want to do a vlookup into this
  • lookup table for each of those items on
  • like what why are you doing this but I
  • covered that yesterday let's just let's
  • just try and figure out how to do this I
  • actually said well power query be the
  • best way to do this but I got stumped on
  • how to do the last part I said what is
  • it he knows it okay if each item ends up
  • at his own road no they have to be back
  • in this original sequence of like oh
  • that's horrible but on my Twitter feed
  • just last week Tim Rodman September 27th
  • finally reading this book I'm guessing
  • it's power pivot alchemy and MrExcel
  • already got his concatenate X wish I was
  • being a smartass when I did this asking
  • for perhaps Roman X but I probably
  • really did want concatenate X and so Tim
  • gave me a heads up that I can now do
  • that in power bi so I went out to my
  • friends Rob Colley at powerpivot Pro and
  • Miguel Escobar and you know they're both
  • authors of great books I have both of
  • these books but this feature is too new
  • not in here book I said hey do you guys
  • know how to do this and Miguel went
  • surprised because Miguel was up early
  • this morning or late last night I'm not
  • sure which one and and sending the code
  • all right so here's here's the plan in
  • power query and this one is so
  • complicated I never write out a plan in
  • power query I just go do the whole
  • things I'm gonna start with the original
  • data add it index column so we can keep
  • the items from an order together split
  • column two rows using a live feed this
  • is the second or third time on the
  • podcast I've used this new feature how
  • cool is that I had a second income index
  • column so we can sort items into the
  • original sequence and then save as
  • connection
  • when I come to the lookup State we'll
  • make it a table query from table same as
  • connection that's going to be the
  • easiest part right there and then merge
  • this query and this query based on item
  • number all items from the left table
  • this is the left table matching from the
  • right replace nulls with the item number
  • we're still up in the air and what we
  • want to do when something's not found
  • for some reason I've asked this question
  • but the person who sent in the file
  • isn't answering so I'm just gonna
  • replace it with the item number
  • hopefully the right thing to do is add
  • more items till the lookup table so
  • there aren't any not founds but here we
  • are and then we're gonna sort by index 1
  • and index 2's that way things are back
  • in the right sequence and then this this
  • was the part that I could not figure out
  • how to do we're in a group by index 1
  • doing the equivalent of a text joint or
  • concatenate X with the character 10 as
  • the separator as the aggregator and of
  • course this is the part that's the hard
  • part but it's the part that's really new
  • here in this set of steps so if you
  • understand what text join does or can
  • conceptualize what concatenate X would
  • have done we're essentially doing that
  • using this step so all right so let's
  • give it a shot so we're gonna start here
  • here's our original data has a heading
  • so I'm gonna format as table control T
  • my table has headers yes and then we're
  • gonna use power query now I'm in Excel
  • 2016 office 365 so it's over here on the
  • left part of the data tab if you're just
  • in straight Excel 2016 not office 365
  • it's in the middle getting transformed
  • if you're in Excel 2010 or 2013 it's
  • gonna be his own tab out here called
  • power query and if you don't have that
  • to have you're gonna have to download
  • that tap if you're on a Mac or Android
  • or any of the other fake versions of
  • Excel sorry
  • no power query for you get a Windows
  • version of Excel and give this a try all
  • right so we're going to do a power query
  • from a table all right and the first
  • thing I'm going to do is I'm going to
  • add an index column and I'm gonna start
  • from the number one alright so this is
  • essentially order one order to order
  • three order four and we're to use this
  • column and on the transform tab we're a
  • split column by a delimiter
  • and they were able to detect that it is
  • a line feed is the delimiter I love that
  • power query is detecting this now why
  • doesn't Excel text to columns or yeah
  • text to columns figure out with the the
  • delimiter is and each occurrence when
  • splitting the rows and using a special
  • character alright so all that's good now
  • watch what happens here
  • we have 999 rows but now we have far
  • more than that so every item in that
  • order number is now its own row now the
  • person who asked this question doesn't
  • want it to be its own row but we're
  • gonna have to make it be its own row so
  • we can do the join
  • I'm gonna add a new index column here
  • add column index column from one and so
  • we have these are essentially the order
  • numbers and then these are the sequence
  • within the order because I've determined
  • it later these are in some other order I
  • I don't know what order they switch to
  • but here we are
  • alright so home not the close and load
  • button but the close and load drop-down
  • and close and load - I don't know why it
  • takes 10 seconds for them to display
  • this dialog box the first time we're
  • gonna only create the connection click
  • OK beautiful so that's table 1 table 1
  • now we're gonna go to our lookup table
  • lookup tables going to be easy to
  • process we're gonna format this as a
  • table ctrl T click OK data or power
  • query if you're not over from table this
  • is going to be called table 2 let's call
  • it lookup table perfect close and load
  • close and load to only create a
  • connection alright now we have our two
  • bits over here and I want to merge those
  • two so we're just gonna go to a new spot
  • and then data get data combine queries
  • we're gonna do a merge and the table on
  • the left is gonna be table 1 that's our
  • original data and we're gonna use this
  • item number and we're going to marry
  • that up to the lookup table
  • and that item number is really
  • non-intuitive that you have to click on
  • the items in both cases to define what
  • the key is and an outer join all from
  • the first matching from the second and
  • see there's 40% of these that are
  • missing from the lookup table this is
  • all fake data but the original data had
  • 40% missing from the lookup table
  • as well really kind of kind of
  • frustrating all right so here's our item
  • number our two index fields and then our
  • lookup table here I'm going to expand
  • that and ask for the description all
  • right you see we have a bunch of nulls
  • here all right so we're gonna do a
  • conditional column conditional columns
  • going to say look at this column if it's
  • equal to null then bring this value over
  • otherwise use the value that's in that
  • column so here under add column will do
  • conditional column nice little UI
  • that'll walk us through this
  • if the lookup table description equals
  • null then we want to use a column here
  • of items
  • otherwise we want to use the column
  • called lookup description and click OK
  • and there we are there's our custom
  • column with either the new value from
  • the lookup table or the original value
  • if it's not found at this point we can
  • right click and say that we want to
  • remove this column was a temporary
  • column was a helper column now that we
  • have what we need we don't need that
  • column anymore and actually at this
  • point I don't need this column anymore
  • either so I can right click and remove
  • that column all right now we have our
  • data here I want to sort it by the
  • original index so sort ascending that
  • gets our data into the right sequence
  • and now that it's sorted I can actually
  • right click and remove that column all
  • right now we're at the point where for
  • every item each order number so this is
  • order number one let's say I want to
  • have these four items separated by a
  • line feed character now what I was
  • hoping to be able to do was to come here
  • to transform instead if we wanted to
  • group by and that there'd be some magic
  • here in the group by I would say I'm
  • going to concatenate or text join all
  • those things but it doesn't work alright
  • so here's the the set of steps that are
  • new to me that allow this to happen
  • first thing we want to do is we're gonna
  • create a brand new column that column is
  • just gonna be called a table column and
  • we're gonna say all rows and click OK
  • okay so when we look inside at this
  • table we see that we have two columns
  • one called index and one called custom
  • and we have to remember that name there
  • all right and this table unfortunately
  • does not work with structured column see
  • extract values is grayed out so this
  • doesn't work with a table it has to work
  • with a list I have to convert this table
  • to a list and this is the part I
  • couldn't figure out and the part that
  • Miguel filled in for me so I'm gonna
  • create a custom column here
  • and I'm gonna call it a list column and
  • we're use a function called table dot
  • column and the table is the thing called
  • table column and then which column in
  • there is the thing called customer click
  • okay all right and now these are instead
  • of a table
  • it is a list we're home-free now
  • transform structured column I'm gonna
  • extract values I'm going to create a
  • custom delimiter using special
  • characters insert a special character
  • line feed and click OK and it gives me
  • what I'm looking for so here's my
  • original order number at the table we
  • don't need anymore and right click and
  • remove that and we've now have our
  • original data using the lookup table
  • where we need it all right so I can
  • right-click and remove this all right
  • and then finally home
  • just straight close and load which
  • brings it back into a table in Excel all
  • right but it doesn't look like it worked
  • does it that's because by default this
  • table does not have wrap text turned on
  • it's a home wrap text and we now have
  • our new data doing the equivalent of a
  • vlookup for each item in the list and
  • when an item is not found the original
  • item number is still there so someone
  • can go piece that back together now the
  • beautiful thing with power query is that
  • while it took us some time to get this
  • set up the first time the next time we
  • download this list we just copied here
  • and we can even edit something let's
  • let's change one so mangoforty 954 will
  • take that 703 6 and change it to 49 54
  • all right so now the underlying data has
  • changed all we have to do is come here
  • to this and click the refresh which will
  • refresh all of these items
  • and we get here sheet 11 and that second
  • item has changed to a mango all right
  • take some time to set this up once but
  • once you get it set up it's just a
  • simple matter of refreshing the data and
  • power query will go through all all the
  • steps now hey this is the point where I
  • usually ask you to go buy my book but
  • today let's instead ask you to go buy
  • Miguel's book Miguel Escobar and Ken
  • polls right this excellent book on em is
  • for data monkey the best book there is
  • on power query go check that out all
  • right wrap up - today is really long
  • episode yeah we have a viewer download
  • data from a system where each item s
  • separated by an alt enter and we're
  • trying to do a vlookup for each
  • individual item build a solution today
  • using power query including the
  • structured column tool of extract as but
  • that only works on a list not a table so
  • I to use the table doc column function
  • to convert the table to a list well hey
  • I want to thank you for stopping by
  • we'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2151.xlsm

Title Photo: Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.