Power Query: "New" Combine Binaries Behaviour

[Datatouille]

Hello everyone,

Did anybody try to append multiple sheets within multiple workbooks since the "New" Combine Binaries Behaviour ? (Power BI Desktop - November 2016 update).

Let's say I have 4 distinct workbooks (called A, B, C and D) and each of them has 4 identical sheets (W, X, Y and Z).

Basically, I need to append 16 sheets (4 workbooks x 4 sheets).

The "New" Combine Binary behaviour forces a parameter... e.g the user has to chose a single sheet (here between W, X, Y and Z) as "an example".

Thus, the result of the appended query contains the 4 workbooks but only for the selected sheet...

What If I want the user to be able to append everything straight?

In my case, I had to modify the steps in the Query Editor in order to append everything. But it requires a minimum of Power Query knowledge and it is definitely not appropriate for someone (me) who tries to demystify Data Prep' and teaches Power Query to people who are discovering the tool !

Any suggestions ? 

Cheers!

[guptasunal]

@Datatouille,@MarcelBeug If the sheets increase within an excel file over the time, would the data from those sheets be included in the data model via standard refresh, or is there any workaround to have the sheets added dynamically?

[MarcelBeug · Accepted solution]

The users shouldn't choose a single sheet, but the container.

In order to boost the educational value: how about teaching them to create the Combine Binaries Behaviour themselves?

It is clearly illustrated in this 4 minute video (and 6 seconds).

[Datatouille]

Hi @MarcelBeug

Thanks a fot for your reply.

Actually I still have a question.

When the user selects a single sheet, Power BI correctly identifies the headers and the append works fine.

When the user selects the whole container, Power BI does NOT identify the headers of each file...