Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply

Power Query: "New" Combine Binaries Behaviour

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!

 

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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

 

Combine binaries screenshot.png

 

 

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).

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
guptasunal
Helper I
Helper I

@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
Community Champion
Community Champion

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

 

Combine binaries screenshot.png

 

 

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).

 

 

Specializing in Power Query Formula Language (M)

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...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.