Forum Discussion
Excel formula to determine missing numbers
You made this extremely helpful & somehow I have messed it up. I copied your work, but i keep getting an error message. No doubt some basic human error on my part, but I admit my skills with Power Query aren't quite up to par, as they should. Since I can't attach a 'clean' copy of my sheets for reference, I've included the screenshots.
I've already tried several times to reply but for an unknown reason my replies in the community weren't posted.
In the screenshots i don't see the error message that's returned. However i see that there's a table named "Tabelle1" with headline "CASE NUMBER" and with a cell containing "----" (exactly 4 times "-") in sheet DATA ENTRY_ALL. Can you copy the M code from my attached file and then load your table into the Power Query Editor and paste the code into the Advanced Editor of your file. After Start -> Close and load the result should look like in my sheet.
I know this shouldn't be so hard, and I apologize for that. Disregard my previous images. I realize now that part of it was just redundant. I have the full running list of case numbers on the DATA ENTRY_ALL sheet in column S. It has been converted to a table entitled 'Tabelle1'. Then, I go to my blank sheet entitled 'Case #'s Missing' cell A1, click on the Data tab, 'Get Data', 'Launch Power Query Editor', 'New Source', 'File', 'Excel Workbook', select my current workbook, Import, under Display Options select 'Tabelle1', OK, that column of data appears, then I click 'advanced editor', I copy/paste your code over what currently displays, click DONE, and get the below error message. What is this novice doing wrong?
My assumption is that there are empty cells in Tabelle1 like in the sample file. Could you try again with the M code in the attached file where i've added a line that removes the empty cells from the table.