Power Query not showing all the cell data

Question

I have combined 35 workbooks using the new get data feature. I have these workbooks in a folder.  Each workbook has the same column names. When I combine the workbooks in power query I am getting "null" in some of the cells where there is text in the original workbook. Any idea why this is happening and how to fix it?

Answer 1

Hi Terrinbt,

Based on your description, when we tested at our by combining the different Excel workbooks having the same columns names using Power Query, it combines the Excel workbooks without issue.

Based on our test, the null values appear when the columns names are different in the particular workbook with reference to the selected Object.

                     

So, we suggest you check Excel workbook and compare the column names with selected object. If the columns names are same, remove the column name and re-enter the column name> save it. After saving, go to Excel worksheet where you combined all workbooks> Go to Query Tools> Select Refresh.

Please let us know the result.

Regards,

Chitrahaas

Answer 2

Everything is formatted the same. It is the weirdest thing. After I have selected the folder to import the data from and the sample sheet view comes up when I click on file A to use as the example all of the information shows up in the window. I click combine and everything combines. The information from all cells is showing but files b and c are missing some data information. If I go and select file b as the sample file all the information shows up and when I combine all the workbooks again now I am missing data from file A. Any reason why this is happening?

Answer 3

Hi

Overall that process is very reliable. There can be issues of course but up to the point of loosing data... I suspect that would be known by know

1 (Edited) - Other reasons that can "generate" null values when querying one or more workbooks are:

1. Merged cells. Do you have any?
2. Cells with Error values, i.e. #DIV/0, #REF... Do you have any?

2 - To test: instead of combining 2, 3... workbooks just query 1:

Data > Get Data > From File > From Workbook. Rest of the process is the same but Load instead of Combine

Do you get all the expected data?

3 - To test: still querying 1 workbook, instead of clicking Load after the sheet selection click Edit (this open the Power Query editor)

If you scroll down do you sell all the expected data? If you have cells with Error values (as discussed in #1) you will see Error in the Power Query editor ==> Error values are loaded into Excel as null values

4 - If issue not identified with the above point please Zip 3 workbooks (highlighting in one of them the missing info. after you combine) and upload + share the Zipped file - see How To here if you want to do it with OneDrive

Answer 4

It still is not working. I have created 31 workbooks in a sharepoint site with office 365. Those 31 workbooks are in their own folder. These are the steps I follow.

1. • Open a new excel workbook and click the "Get Data" Feature
2. - Click from File; then select from Sharepoint folder
3. Paste the root URL for SharePoint
4. Click the edit button to remove any files that I do not want in the query (it shows every file I have and have deleted)
5. Click the "Content" arrows to get a preview of the sample file and what will be merged
6. A pop up will appear, click on the sample file and the sheet that is being used, verify that is the correct data
7. The merged files appear along with the source name in the first column. From here I can edit/remove columns etc.
8. Click close and load and my new merged workbook appear.

Everything merges BUT some rows have missing information It shows as NULL. Not all rows, it seems like it affects the first 11 rows. The formatting is the same for all workbooks. I have check. I have even deleted the workbook that would have the missing information (only shows as missing or NULL in the merged query, mot in the original workbook and then recreated that workbook and still the same cells show up as NULL when I refresh the connection or if I create an entirely new merged workbook. I have entered information in some of the other workbooks and refreshed the connection and they seem to work. It is affecting two of my workbooks, that I know of. 

This is very frustrating, the feature is great when it works properly. I have tried everything I can think of. I have downloaded the file to my drive on the computer and instead of pulling the data from a SharePoint folder I select from folder and I have the same results. As these 31 workbooks continue to be used and I continue to refresh the connection, it will be harder for me to use the merged workbooks as I need them to. 

Any help is appreciated!!!!

Answer 5

Hi - always a good start, isn't it?

Re. Any help is appreciated!!!!

TBH I don't know where to start... As my English is far from being my 1st strenght I'll be direct: even if I quickly read your last reply (there's a new info. ==> workbooks stored on a SharePoint site) I haven't seen information that relates to the tests I suggested & the corresponding questions - I might have missed a point but I don't think so

Anyway, last point was:  If issue not identified with the above points please Zip 3 workbooks (highlighting in one of them the missing info. after you combine) and upload + share the Zipped file

• see How To here if you want to do it with OneDrive 

Please understand I'm trying to help (I have a certain level of expertise in Power Q.) but if you do not follow/answer what's asked there's no way we'll identify and hopefuly fix this issue...

As I don't want to waste your time nor mine - last call on my side: please Zip 3 workbooks (highlighting in one of them the missing info. after you combine) and upload + share the Zipped file - see How To here if you want to do it with OneDrive

Hope you understand & appreciate

Best regards