Split Column function not creating the correct number of columns on refresh

[pAAmckelvey]

Trying to create a robust reference table to assist in filtering data in the table it is based on.  For the most part it works which is great but the problem arises during the refresh process which is the problem I am trying to solve.  Here's the setup:

1. In Power Query Editor using the right click option on one of the tables called "Features" right-click the menu item called Reference to create a new table. 

2. Isolate the data by removing all but two columns, the ID column, which will allow us to connect to the original table, and a column called Tags. 

3. Split  the Tags column using a delimiter, in this cases a semicolon,  with the option for "Split at each occurrence of the delimiter" checked. 

4. Un-pivot the tags columns that were created by split column function in the last step.  

5. Remove the column that is now full of Column headers that were left from the un-pivot function.

Now all that is left in the reference table is the ID column and a Tags column

This is where it gets tricky.  The Tag data comes from a system where the end users can add tags as they see fit to organize their work.  This means the number of tags on any given item will change from time to time.  What happens now is if a user adds a new tag then one of the tags in the delimited field doesn't show up.  When setting up the split column it works great, however, later on when a data Refresh button is clicked it is not getting the new tag item because when the tool originally did the split it was for only 3 columns, which at the time was the most number of tags for any given item.  

My question is, when a data refresh is done, does it re-evaluate the "Split Column by Delimiter" step which contains the command for splitting at each delimiter?  At the moment I have to manually walk through the steps again to get it to recognize that there are now 4 tags in some of items which kind of defeats the purpose of the Split at each delimiter in the Applied Steps which should get applied during every refresh, correct?

[rasanen]

Hello, I am facing a similar kind of a problem when trying to divide the name of a certain product by delimiters ("/" in this case). 

I used this as quoted above 

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Tags", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Tags.1", "Tags.2", "Tags.3"}),

with one extra column, but for some reason the third column includes errors in each of the products when the third column includes more than just numbers (so "12" is okay, but "A12" is not). This does not happen in other columns.

I can change the type of the data included in the column but still won't work when I close and apply the changes made in the data. Any ideas? 

[KristianVangen]

I see you figured it out, but I would like to show my solution to getting the correct number of columns. This doesn't solve the issue when the data is updated and there should have been more columns than before, but at least it produces the correct amount at that spesific time.

I figured out that if my table is to big, the "Split Column by Delimiter" doesn't check all values for the one with most delimiters. To get around this, I created a new column with the number of "delimiters" and sorted Z->A on this column. Then I went to "Split Column by Delimiter", and it found the highest number of necessary columns to produce.

Sorry about the language being in Norwegian, but I hope it's understandable. My delimiter here is \ .

= List.Count(Text.PositionOf([Column1],"\",Occurence.All ))

[pAAmckelvey]

Thank you Kristian for your response, a clever approach! This could help me in other ways so it is always good to hear others solutions.  No worries on the Norwegian, hope to visit Norway in the future. 

[v-kkf-msft · Accepted solution]

Hi @pAAmckelvey ,

You can try to split the column into rows, so that the number of rows will change with the number of tags, and there is no variable limit.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pAAmckelvey]

Thank you Winniz, I'm not sure why I was so hesitant to try the Rows option and it alleviates the need to unpivot as well!

Patrick

[PhilipTreacy]

Hi @pAAmckelvey 

In your query, you need to manually change the split column step.  Right now it will looks something like this

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Tags", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Tags.1", "Tags.2", "Tags.3"}),

You can see that it has a list at the end {"Tags.1", "Tags.2", "Tags.3"} which means it will split the column into 3 columns with those new column names.

Delete that bit so the step looks like this

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Tags", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv))

and the query should now split the Tags column into as many columns as needed, regardless of how many tags are in the column.

Regards

Phil

[JG106452]

This does not work, instead, the column is entirely removed since there are no defined headers