Detecting sources files with missing fields

[arcegabriel]

Looking for some help from the experts

I have a power query that pulls files on Sharepoint. It will pull out a particular table on them

The tables are supposed to be the same but not always are. 

Say most files have fields A, B, C, D, E (the sample file as well)

And then there is one file incorrect.xlsx with fields A, B, C, D, Z

I woould like to have a way to identify any such a file. I figured that when I run the query, it just shows for the incorrect.xlsx including the fields A, B, C, D, E and E is empty

Is there any way to detect this incorrect.xlsx?

[arcegabriel]

Hi,

Thanks for trying this out. The question is that in your example. These two result on teh same:

1. If the column is missing in exce2.csv it will show in end result as "null" (what you did)

2.  if the column exist but has no values in exce2.csv it will ALSO show in end result as "null" 

My question was whether there is a way to different case #1 vs #2

[Syndicate_Admin]

Hi @arcegabriel ,

As shown in the figure below, there is a incorrect field column in EXCEL2.CSV. The fields in excel1.xlsx are all correct, then when you combine files, select excel1.xlsx as the object template to combine.

Please see the introduction in the underlined section, select the object to be extracted from each file.

The result is as follows, the incorrect field is not extracted.

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.

[Anonymous]

Hi @arcegabriel ,

As shown in the figure below, there is a incorrect field column in EXCEL2.CSV. The fields in excel1.xlsx are all correct, then when you combine files, select excel1.xlsx as the object template to combine.

Please see the introduction in the underlined section, select the object to be extracted from each file.

The result is as follows, the incorrect field is not extracted.

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.

[CNENFRNL]

= List.ContainsAll( Table.ColumnNames( xxxtable ), {"colA","colB","colC"} )

test if column names of a table contain all of {"colA","colB","colC"}

[arcegabriel]

Thanks bu that is not addressing my question. The problem is when you combine source files and a sample file is used as a sample (example https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries)

In that case all files are read using the sample file structure with the results I described on the OP