Forum Discussion

alex.mohr's avatar
alex.mohr
Copper Contributor
Sep 12, 2017

excel table adding extra data after end of "real" data

Hello,

I need to combine multiple data sets with different column headings in order to create a pivot table.

I have found power querry and it works with a few of the tables. I have created tables that pull information from sets of data. They all have blank columns and calculated columns. Some of the calculated columns return #N/A after the end of the data set or in the calculated date columns they return 1's and 0's. When I hit control+end in either the data sets or the tables it takes me to row 64214 and column AA or something like that. Because of this I can't stack my tables.

How do I return only the legitimate data to these tables and stop the calculated data at the end of the legitimate data? If there is an easier way to get what I want I'm all ears.

Thanks,

Alex

excel
  • Brian Spiller's avatar
    Brian Spiller
    Brass Contributor
    Sep 13, 2017
    Possibly some hidden characters in some of the cells. I usually have to fight with CHAR(0160). Its a little front end work to validate the data and see if any cells are populated with non-visible characters like that. Check for string lengths greater than the visible characters or try a search and replace. TRIM and CLEAN do not get rid of all the offending characters but is a good start. TRIM and CLEAN are available in PQ as well as the replace. You can also have a calculation in PQ that kicks back an error for the rows and then remove the errors.

Resources