Split column by delimiter does not recheck data if there are additional delimiters added after initial step.

Question

I was working with a dataset where I used Split Column by Delimiter to break information into separate fields. At first, everything worked fine. Later, I created a TEXTJOIN formula to pull data from a spill range generated by a FILTER function.

When I added a new name to the source information, the spill range updated correctly but the Split Column by Delimiter step in power query didn’t refresh. No matter how many times I updated the data, that split column remained stuck on the previous version.

In the end, the split column simply wasn’t recognising the new data at refresh, even though the FILTER and TEXTJOIN formulas were working as expected.

Any fixes?

Summary : Split column by delimiter does not recheck if there are additional delimiters added after initial step. It seems it can only be used on static data.

Answer 1

Hi,

You may use SplitColumnByAnyDelimiter. For more help, share some data to work with and show the expected result. Share data in format that can be pasted in an MS Excel file.

Answer 2

Hi @Kofoworola George-Taylor,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, this is an expected behavior of Power Query when you use Split Column by Delimiter. When using Split Column, Power Query generates an M step for example:

=Table.SplitColumn(Source, "YourColumn",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"YourColumn.1","YourColumn.2","YourColumn.3"})

This will lock in the expected output schema, so if in the later refreshes you add more delimiters than the split step was built for, Power Query’s Table.SplitColumn M code will ignore extra split values. As expected, your FILTER spill and TEXTJOIN are working properly, but Power Query is still outputting only the original number of split columns and discarding any new parts beyond that.

To make a Dynamic Split step, you can try following the guidance with detailed explanation in this article: Split a Column Dynamically - Power Query - Tips & Tricks 6

(Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.)

I hope this information will help. If you have any questions or need further assistance, please feel free to reach out again on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.