When first 2 rows are empty from each file and third row is header?

[Frixel]

Hi all,

How to act if of every document that comes in the folder the first two lines of lines are empty/not important and the third line are the headers?
Can I tell power bi that it should only look at every document from the third line?

Like this when i have combine.

[v-henryk-mstf]

Hi @Frixel ,

Has your problem been solved? If not please point out, looking forward to your feedback.

Best Regards,
Henry

[Frixel]

Strange is that when i have 1 file (sample) in the folder it look that it is okay. (green line)

When i copy that file with only ' copy'  in the file name it is also okay with 2 files in the folder. (also green line)

When there is a new file (format and colomns exactly the same as the sample) with part of new name there is a error to see in the last row in the query where the new rows must begin.

I hope you understand me.

[jdbuchanan71]

Are you combining them together into a single table so that files 2, 3, 4 etc have the first 2 rows blank?  If that is the case, after you remove the first 2 rows and set the headers you can filter NULL out of the first data column to get rid of the other blank rows.

[Frixel]

Before i can do that i see there are errors on the 2 file`s in the folder.

what could be the cause of that?

In the step before it looks fine, because there are 'green' colomns.

By the step for combine i think it is strange that by 'sample file'  the whole filename is but under it by 'display options' there is just a part of the name. (not after the second _) 

[jdbuchanan71]

@Frixel 

You can tell PowerQuery to remove the first 2 rows then promote the top row to header.

[Frixel]

Thanks,  i see that option now.

And that works fine, but when there is a new file in the folder then power bi give a error on that file.

[VahidDM]

@Frixel 

Did you add that remove the first two rows step to the "Transform Sample File" query? Add your steps to the "Transform Sample File" query; then, those steps will be applied to all files from that folder.

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

Appreciate your Kudos!!

[Frixel]

Hi @VahidDM 

Ehh, i do that in the step next when i combine the files.

[VahidDM]

Hi @Frixel 

What error have you faced?

Do those files have the same sheet?

Appreciate your Kudos!!

[Frixel]

Hi @VahidDM 

Now i see that the filenames are different but the sheetnames are the same.

I think That is the problem?

[VahidDM]

@Frixel 

The file names can be different but the sheet name should be same. the might be when you added a new file the sample file changed and because of that some steps don't work well.

Check that.

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

Appreciate your Kudos!!

[Frixel]

Hi @VahidDM 

I really don`t know what goes wrong.

The sample file is not changed and the new files are the same as the sample.

[aj1973]

Hi @Frixel 

The name of the excel file should stay the same as the original file you imported and combined into Power Query otherwise Power Bi won't reconized it. 

[Frixel]

Hi @aj1973 

You say: The name of the excel file should stay the same  but my file`s  in the folder are like this.

LS_20211010_16h33m.xlsx

LS_20211010_21h14m.xlsx

LS_20211011_07h34m.xlsx

LS_20211011_08h01m.xlsx

Before combine i have the text filter to: Contains  LS_

Thats okay?

[Frixel]

After searching on this forum i changed this:

 to this:  

I don`t know what it is doing but it works.

Can somebody explain what it is doing in this query and do i good for this?

[v-henryk-mstf · Accepted solution]

Hi @Frixel ,

It is not 1 query, but 4 queries that are grouped. It contains the logic required to combine the files from your folder.

The set of queries is created automatically when chosing "Combine Binaries" in the column header of the column in the navigation table with the files in your folder.

You may get rid of it by converting the files to tables, using native functions like Csv.Document or Excel.Workbook, depending on the file types in your folder.   Here is the similiar solution.

Best Regards,
Henry

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