Blank cells in Power Query Merge results

[aflintdepm]

I am using Power Query in Excel to merge multiple versions of a report that I pull from several locations.  Every report is in the exact same format with the exact same headers.

In one particular merge process, the resulting file has blank values in the Client Name column.  When examining the source file, there are values in that cell, so it appears that those values are being dropped during the merge process.  The rest of the row has the correct values, just the name column is blank.  This does not happen on every report, and there is no pattern or predictability.

What can I do to troubleshoot this, as this data needs to be in the final merge?

I have to redact for sensitvity reasons, but here is a sample

Thanks

[Anonymous]

Hi @aflintdepm ,

Is your Merge setup correct? Did Join Kind choose the right one?

Merge queries overview - Power Query | Microsoft Learn

Sorry I still can't understand your sample. If you can, please provide some dummy data, and the correct expected outcome after the merging.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[aflintdepm]

Thank you for trying to assist.  I'll provide as much information as I can.

When I set up the query initially, this was my process:

1) In Excel, I selected the Data tab, then Get Data

2) I selected From Folder where the folder contained all of the files I want to merge

3) I was not given the option to select the Join (that I saw)

The only way I've been able to get this to work is to add each file as a separate data source, then use "Append Queries as New", but this is not the preferred method, as new files may be added in the future.

To clarify, it is not skipping rows- it is just leaving 1 cell in the row blank.  All cells to the right of my example screenshot contain the correct data.

Unfortunately, I can't upload sample data because it only happens with this specific file set and there is too much sensitive data to redact without substantially changing the sheets.

I use this process with multiple folders and all of the others work as expected, which is what makes this one scenario so frustrating.