Why Power Query not pulling in all the data when I combine data by folder

Clovie 10 Reputation points
2023-07-13T14:59:46.0833333+00:00

Power Query not pulling in all the data.

I am attempting to pull in data from ~25 macro enabled excel workbooks which are located on an office 365 share point site via a data query from folder. I have the share point folders synced and the files are all visible. There is one tab in each of the 25 files that I am pulling into single excel file via the power query. The tab and column headers are all named the same.

Everything appears fine at first, I can transform all the files, combine them using just the tabs in question and I get a nice export. But when I did a pivot off the queried data, I noticed stuff was missing. So I opened the source file, and confirmed data was there (it was).

The missing data isn’t consistent. As in column F for example is populated in all 25 workbooks, but on 3 of them i the query, that data is “null” while it comes across fine in the other 22.

This is when I try to do a power query “by folder”. So to test, instead of by folder, I did the 3 files that were generating null in the query “by file” instead. So just querying off one file instead of a folder worth. And in that scenario, all the data came through perfect. The column F data that would null in the by folder query worked fine in the by file query.

There is something occurring in the folder query that is causing data that is in the source files to null out, but if I query the file directly it works. It is highly preferable to have all data in a single refreshable tab than querying 25 workbooks directly into 25 tabs.

Any insight to what would cause the data loss?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,154 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
41,983 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,757 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Lz._ 9,006 Reputation points
    2023-07-14T06:01:20.8833333+00:00

    Hi Clovie

    Only reason I can think of is (wrong) data Type(s). With your 'By Folder' query, go to Data (tab) > Queries & Connections and check if the query reports Errors as in the below example:

    Sample

    In the above example my 'By Folder' query ends with a Changed Type step that says the [Amount] column is of type number:

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",
        {
            {"Source.Name", type text}, {"ID", Int64.Type}, {"Date", type date}, {"Customer", type text},
            {"Order ID", Int64.Type}, {"Qty", Int64.Type}, {"Amount", type number}
        }
    )
    
    

    but my 2nd file (ExcelSourceFile2) is as below:

    Sample2

    Cf.: Missing data in query result

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.