I am trying to remove the last 16 characters of a custom column in Power Query. In Excel, I use the formula =right(A1,LEN(A1)-16). I understand there is no LEN function in Power Query, so have tried using Text.Right([Product]),-16 and variations of that string to no avail, while I do not get a syntax error for the formula, once applied I get Text.Right not recognized.
Example of product info:
Vitamin C 24ct - 0011443388446
Herbal Formula 20ct - 9944881133005
I want to remove a total of 16 characters which includes all blank characters, the dash, and the upc numbers. This seems like an easy issue but can not seems to get this to work.
Any suggestions would be appreciated.
So I found a solution: =Text.Start([Product],Text.Length([Product])-16)
This worked to remove lines that had items with UPC's, however some lines were category labels without UPC's (i.e. VITAMINS) so now I have "error" lines. Will this cause the below error??
When I went to Close & Load - Only create connection, I am getting the following error: [Expression.Error]The key didn't match any rows in the table.
If the errors in my custom column are an issue, is there any if/then statement that would resolve my expression error?
Hi Cindy,
Have you tried splitting the column by delimiter using the - as the delimiter? Then you can 'format' the column with Trim to remove the extra space on the end.
Mynda
Hi Mynda,
Thank you for your response & excellent site! I share your site with other Excel minded co-workers.
I ended up using an if-then-else statement and resolved my error lines. However I am still getting Expression Error mentioned above, and download failed. My data is linked to a folder on a cloud based share site, so co-workers are able to share files/work . . . not sure if this could be causing the issue download failure & Expression errors!?
Thank you for any suggestions/direction.
Thanks for your kind words, Cindy! Glad you figured out a solution.
In regards to your expression error, yes, Power Query cannot access an open file, so it may be having issues with the cloud based folder. You could try syncing the folder to your desktop and then referencing the files using From File > From Folder to the desktop location.
I've read other posts relating to this error and some have reported that refreshing the query inside the query editor fixes it, so it may be as simple as that. If you get an error refreshing the query then it might be a permission restriction.i.e. your PC doesn't have permission for that folder, or you need to enter login credentials for the query to work. If not, try moving the files to a synced folder.
Mynda
Hi Cindy,
When you import from a folder that might have some files open, it's a good idea to filter out the temporary files that are created by excel automatically when a file is open, those are triggering the error.
= Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true)
This way, PQ can access open files. Once those are filtered out, refreshing the PQ editor preview as Mynda mentioned will fix the error.
