Forum Discussion
Costan08
Sep 21, 2022Copper Contributor
Expanding range stated in same cell.
Hello everybody,
I have a question regarding a data table imported from a PDF file.
I have a product catalogue where a range of codes are 'grouped' in a single cell, expressed as 'Lowest number' '-' 'highest number'. This is not true for all rows in the column.
As the list is over 1100 rows long, is there a way to automatically detect the cells containing ranges and expand them so that every code is individually listed in the range order?
thank you
Costan08 Perhaps the attached file is what you want. It allows for the five code structures that I mentioned earlier. It seems a rather clumsy approach, though.
The combined tables from the PDF contained 2238 line-items. The query produces 14648 line-items (216 more than in my previous attempt). Difficult to be sure that this one is complete now. Check it thoroughly please!
- Riny_van_EekelenPlatinum Contributor
Costan08 If you are confident with Power Query, please see the attached (very simple) example. I took the liberty to assume that the table might contain more than just one column.
- Costan08Copper Contributor
I have never used power query nor the microsoft techcommunity.
Therefore it's difficult for me to understand how you achieved the result you posted, which is the exact answer I am looking for. However, I cannot seem to post neither pictures or the list I am trying to parse.
Could you please explain the process you went through?
Thank you
- Riny_van_EekelenPlatinum Contributor
Costan08 Try sharing the file on OneDrive or similar. Otherwise, click on my "name tag" and send it to me via private message.
Power Query take a bit of an effort to learn, but once you do, you "can't live without it".