Forum Discussion

Costan08's avatar
Costan08
Copper Contributor
Sep 21, 2022

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!

     

    • Costan08's avatar
      Costan08
      Copper Contributor

      Riny_van_Eekelen 

       

      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_Eekelen's avatar
        Riny_van_Eekelen
        Platinum 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".

Resources