Download "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement."

Please wait. We're preparing links for easy ad-free video watching and downloading.

Previous video: Power Query - Comment Importer automatiquement dans un classeur une source d'une extraction externe Next video: EXCEL - Conception Tableau de Bord avec Design (Module #2)

EXCEL - Conception Tableau de Bord avec Design (Module #2)

EXCEL - Conception Tableau de Bord avec Design (Module #2)

Channel: Learnaccess

Power Query - Comment Importer automatiquement dans un classeur une source d'une extraction externe

Power Query - Comment Importer automatiquement dans un classeur une source d'une extraction externe

Channel: Learnaccess

Office 2010: The Movie

Office 2010: The Movie

Channel: Dennis Liu

How to View Blocked Websites Using a Proxy Server

How to View Blocked Websites Using a Proxy Server

Channel: usemybox

Bureautique

Excel

Pack Office

Word

Access

Powerpoint

Power Pivot

Power Query

BI Desktop

power query in power bi

power query m code

power query tutorial

bi desktop tutorial

power bi desktop download

power query transform data

automatiser la consolidation de fichiers

consolider des fichiers excel

excel consolider des donnees

excel tutoriel francais

excel consolider plusieurs feuilles

excel consolider plusieurs fichiers

formation power query

00:00:01

hello everyone and welcome to this

00:00:03

new video so today video

00:00:04

dedicated to the connecting rod from Microsoft and

00:00:07

in particular here at puerturie

00:00:10

so

00:00:11

how to try here to make a request

00:00:14

which allows you to combine several

00:00:17

workbooks

00:00:20

at the same time and one each of the

00:00:22

workbooks we will have several sheets

00:00:24

which will be found in a folder so

00:00:27

how to combine them, merge them and

00:00:28

have them in one and the same table

00:00:30

so we will see here how we

00:00:32

go about doing this

00:00:35

consolidation this automatic synthesis

00:00:36

and you will see that we we're going to do we're going to

00:00:39

do everything thanks to a powerkery request

00:00:41

without having to type any macro command

00:00:43

in particular so we're going to I'm going to

00:00:46

show you the file already so we're going to open a

00:00:47

folder here here we have a folder here

00:00:49

which is called combine

00:00:51

file sheet here's inside

00:00:54

for the moment we have three Excel files

00:00:58

and one for the month of March ok I'm going to

00:01:01

open one to look at the head it has for

00:01:04

you open an Excel workbook here we have

00:01:07

several sheets here we have a store

00:01:09

a store 2 store 3 Store 4 here

00:01:13

and here inside we therefore have the same

00:01:15

titles we have here data with

00:01:18

dream products and amounts here

00:01:20

and we do not have the same number of lines

00:01:21

of each one and so straight away OK

00:01:24

here I am I would like to recover

00:01:26

all these sheets which are in

00:01:29

this file okay the combined ones already and

00:01:34

at the same time recover all the

00:01:37

files which are in this same

00:01:39

folder so if tomorrow I do the month

00:01:40

of April it would have to recover

00:01:42

all the sheets of this April file

00:01:44

and who puts the consolidates and who

00:01:48

see them here how we do to check so here

00:01:50

we go so here I'm going to

00:01:52

insert that in a sheet so we're going to

00:01:56

go to the Data tab

00:01:59

in the group retrieve transformed we

00:02:01

will tell it to obtain data

00:02:03

from not a file but a

00:02:06

folder since we have the data in a

00:02:07

folder

00:02:10

okay and we will show it the folder

00:02:12

in which so here it is in my

00:02:14

miserable documents we are going to go to

00:02:17

power pivot and from a pivot we have a

00:02:19

source and in this source we have the

00:02:20

famous folder which is called combine

00:02:22

file and sheets

00:02:23

I am going to open it

00:02:27

there it will start to launch the editor

00:02:30

so that yes there it is there it shows you the

00:02:32

three files which see mars files

00:02:37

with here the data we are not going to

00:02:39

combine yet we are not going to change we are going to

00:02:40

first transform because we are going to have to

00:02:41

recover full of things

00:02:42

we transform

00:02:46

the data

00:02:48

so at this stage from there we will have

00:02:51

here power curry which will appear

00:02:56

ok which will already try here to recover

00:02:59

my sources

00:03:01

yes if we have we have here the data where

00:03:04

my files are located here we see although

00:03:06

when we go to the contains column we

00:03:08

tried small arrows and it

00:03:10

shows you here the files they tell you

00:03:12

that it concerns the files here we have

00:03:15

the file of February we have the file

00:03:17

of a month of March and in displayed so we

00:03:18

must already in the first step

00:03:19

recover all these files with their

00:03:22

content since it is inside and

00:03:25

at the same time their name so for that we

00:03:27

will select the first two

00:03:29

columns what we will need that it is

00:03:31

places the rest is that the extension

00:03:33

the date here it gives you the

00:03:34

properties of Vichy here we are going to

00:03:36

right click on these two columns and we are

00:03:38

going to the link to delete the other

00:03:39

columns

00:03:40

so here we have already recovered we keep that

00:03:43

the part where there is the content and

00:03:46

the columns

00:03:50

then so there we could already at this

00:03:53

step rename so here we are going to put

00:03:55

consolidation

00:03:58

consolidation

00:04:02

consolidation

00:04:08

OK and here we could already here rename

00:04:11

this first step so right click we

00:04:13

can rename the steps of the query

00:04:14

if you want therefore deletes

00:04:20

unnecessary

00:04:22

OK

00:04:23

then so the continuation here we must

00:04:26

try to recover the contents of the

00:04:29

file here so for that we will add a

00:04:31

column which will extract all the

00:04:34

workbooks which are contained in there

00:04:37

ok so for that we are going to go

00:04:39

to the add a personalized column tab

00:04:48

here it will open for you a window

00:04:50

called personalized column and

00:04:52

there we are going to type a formula and the

00:04:54

formula will be to recover all the

00:04:56

workbooks found in this column

00:04:58

happy so for that if tomorrow there is

00:05:00

a fourth file it will

00:05:02

recover them automatically so we will

00:05:04

look for an instruction from the language m of

00:05:06

puerturia which is called Excel workbook

00:05:10

excellent we open the parenthesis and there

00:05:13

it needs

00:05:14

one from another of the workbooks the names

00:05:18

of the workbooks we have them in the famous

00:05:19

content column so we will look for

00:05:21

the content column here double click and it will

00:05:24

automatically feed the formula I

00:05:26

close the parenthesis there and from

00:05:29

there we will do OK

00:05:31

and there we will recover

00:05:33

all the workbooks and create a

00:05:35

column called personalized and

00:05:37

there we will see that if we click on a

00:05:38

cable we will have the content here if I

00:05:41

expand the window we will have the

00:05:43

content of all here the sheets contained

00:05:46

in each of

00:05:48

the tables there are some there are

00:05:51

8 sheets there are others here there are 9

00:05:52

sheets there is one where it has 8 sheets

00:05:55

so you can have as many

00:05:56

sheets as you want it will do it

00:05:58

on its own ok we what we need to

00:06:00

recover so it is now the

00:06:02

content of these sheets so for that we are

00:06:04

going to grab the two little arrows here

00:06:06

and we are going to click on them to tell it to

00:06:08

expand the content okay and we

00:06:11

do OK

00:06:13

so now it has retrieved

00:06:14

all the names

00:06:17

of the sheets that we see okay so

00:06:20

if we can recover here so

00:06:23

we will say add

00:06:26

column

00:06:29

add column to fetch

00:06:30

the files here OK

00:06:34

and then we're going to tell him here

00:06:37

in we're going to rename it so he

00:06:40

called customize developed but here

00:06:42

we're going to put display sizes

00:06:46

here's the file

00:06:48

ok so at this stage here we can clearly see

00:06:51

that we have here

00:06:54

if we go here look at the tables here

00:06:57

now he has given us the content of

00:06:59

each of the sheets so here we go here

00:07:01

there is a column called

00:07:02

customize data or basically here the

00:07:05

content of the files we see here

00:07:07

table one we see here the table we see

00:07:09

here the table we see them here the content

00:07:11

of each one okay so we will have to

00:07:14

develop this part there

00:07:16

but we will also need to keep the

00:07:18

name of the workbook it is moreover we will

00:07:20

reconstruct a date but the the

00:07:23

stores to say which store

00:07:25

it is so for that we are going to select

00:07:26

the three columns that we want to keep the

00:07:29

others we do not need it is

00:07:32

redundant so we are going to right click

00:07:34

delete the other columns keep that

00:07:36

it's columns

00:07:38

when we need from there we

00:07:41

for each of the files that are

00:07:43

there we have here the names of the sheets and

00:07:46

inside we have the content of the facts

00:07:47

so we will have to go and recover

00:07:48

now the content of these sheets

00:07:50

so for that we are going to click on the two

00:07:52

small arrows again to

00:07:54

ask it to expand the two

00:07:56

columns so we need the column of

00:07:58

dreams produced and the column where there

00:08:01

were the amounts so we are going to tell it

00:08:03

to 'use here we will uncheck to

00:08:05

tell it to use the names of the

00:08:06

original columns and not put colonial column

00:08:08

2 and we do OK and there we will have the

00:08:12

detail

00:08:14

so here with the whole column there are

00:08:16

all the dreams and the column where there are

00:08:17

all the amounts

00:08:20

so from there we will have to

00:08:24

tell him but to consider here the

00:08:27

first line here the first line

00:08:29

it is called store product amount

00:08:31

and that so we will tell him to continue

00:08:33

this first as being put in mind

00:08:34

to replace colonel colon 2 people

00:08:36

dislike them and all that so for that we

00:08:38

can either click here on the

00:08:40

table and we use the first

00:08:43

line for the athletes otherwise you

00:08:44

can go to the Home tab and

00:08:46

tell him to 'use the first line

00:08:48

for the there are several ways to

00:08:50

do it we are going to use

00:08:52

and now the first line is going to

00:08:54

go up

00:08:55

so here we have the amounts we have the

00:08:58

products we have here the store so here we

00:09:00

could rename it to tell it that that

00:09:02

it's called the store but it's

00:09:03

not called the store and here we

00:09:05

could tell it that this is the name of the

00:09:06

file so

00:09:12

here it is for my speakers so

00:09:15

now the next step is going to be a

00:09:16

little bit of cleaning my data we

00:09:18

see here there are columns where there

00:09:19

are nulls there are columns where there are

00:09:21

amounts so we are going to put ourselves

00:09:23

here it is enough that there were spaces in the

00:09:26

lines he will put nulls and here we

00:09:28

also have to remove all the

00:09:29

columns where it says product amount

00:09:30

here we are going to find them

00:09:33

product amount so for that we are going to go to

00:09:35

this product column we are going to clean

00:09:37

so for that we are going to tell him I don't want to

00:09:38

the dummies and I don't want the column where it

00:09:40

says product but I keep all

00:09:42

the other dreams I do ok here

00:09:44

from now on we have cleaned the source

00:09:48

directly here so now we have

00:09:51

almost all our file completed

00:09:55

so here we can also develop so

00:09:58

here we are going to put delete

00:10:01

we could also delete

00:10:04

column

00:10:07

here we are going to say expand here we are going to

00:10:10

put in the name

00:10:19

displays sizes

00:10:26

we are also going to rename this step and then

00:10:28

right rename and here we are going to put

00:10:34

promote at the

00:10:37

top

00:10:40

here you have here create a step which

00:10:42

allows you to automatically identify

00:10:44

compromises it will try to detect for

00:10:45

gasoline this step we can

00:10:47

delete it we don't need it

00:10:50

here we had to here we are going to put Rhône here

00:10:53

we are going to put Renault

00:10:56

a text

00:10:59

OK and here we are going to put

00:11:03

delete

00:11:10

unnecessary line

00:11:14

OK

00:11:16

so now we have almost

00:11:18

finalized all that now all that remains

00:11:19

is to take this first column

00:11:21

and try to construct a date

00:11:25

here we have the least the first value

00:11:27

is the months then we have the years

00:11:29

and then we have the extension so we will

00:11:31

already ask it to remove the

00:11:32

extensions point XLS x so for that we

00:11:35

will go to transform

00:11:38

extract

00:11:41

from the first characters

00:11:44

and we will ask it to keep the

00:11:45

first seven so if you are going to type the

00:11:47

number 7 that we want to keep the error

00:11:48

underscore and the year so 4 5 6 and more

00:11:52

one of what it makes 7 and

00:11:53

automatically it will remove everything

00:11:55

that is behind so here we will

00:11:57

put it we will remove

00:12:02

extension

00:12:06

so now we will have to

00:12:08

tell it to separate the months with the

00:12:12

years so for that we always go to

00:12:14

the transformed tab to split the

00:12:17

columns by limiters

00:12:19

and here it has automatically detected the

00:12:22

delimator we do OK and there it will

00:12:24

generate you two columns a column with

00:12:27

the months

00:12:29

a column with the years now

00:12:30

with the months and the years we will be

00:12:32

able to reconstruct a date so

00:12:36

for that we will do it in a new

00:12:37

column so we will go to the

00:12:39

add a personalized column tab

00:12:42

we will give a name to this new

00:12:44

column which will create which will be called

00:12:45

date

00:12:47

and then we will look for an

00:12:49

instruction a function m which is called

00:12:54

so we will look for a formula date

00:12:56

here we will type date

00:12:59

function date this function date it has

00:13:02

need three arguments it is

00:13:03

the equivalent of the tatoucine

00:13:04

Excel function so it first needs the

00:13:07

first argument to find the year so

00:13:09

the year is the column which is called

00:13:10

not file 2 so we will search here

00:13:12

in file 2

00:13:14

comma the month so the month we have it in

00:13:18

the name of file 1 so in the column

00:13:21

my file here indeed the column where there

00:13:23

were the months here we want to reconstruct

00:13:25

a date by putting the first of each

00:13:27

month we are going to type one and we close the

00:13:30

parenthesis so here it will reconstruct

00:13:32

a date from two columns I

00:13:35

do OK

00:13:36

and now it has reconstituted the

00:13:38

date we have here January 1st we have here

00:13:41

the data for the month of February and so

00:13:43

on continuation

00:13:45

so from there we have we have here

00:13:47

we do all that again so here we are going to

00:13:49

tell him split

00:13:53

split year

00:13:57

me okay

00:14:01

so there he had removed them again

00:14:07

we could do it

00:14:17

and so we go here

00:14:20

so we are going to do a little Z control

00:14:23

and you're lying we're going to need it

00:14:26

this last one

00:14:28

we're going to have to recover the data

00:14:31

directly

00:14:39

so here we're going to have to redo

00:14:42

the step we're going to put it here in number

00:14:44

because moreover if we want to

00:14:45

reconstruct the date we saw it so

00:14:48

it happens also often so we're going

00:14:50

to put integer number

00:14:52

and we're going to put here also

00:15:00

like that now he's going to be able to

00:15:01

reconstruct my date otherwise so here we

00:15:04

're going to put we're going to leave it here Charles

00:15:09

digital

00:15:12

and here we're going to him say to add a

00:15:16

column so if we are going to add a

00:15:18

Date column

00:15:24

it is more explicit we understand what

00:15:26

that does so from there

00:15:29

we will now be able to just

00:15:30

clean the data a little bit so

00:15:31

these first two columns we need the most

00:15:34

we right click and we are going to

00:15:36

delete the columns

00:15:39

we have the stores we have the products

00:15:42

we have the amounts if I want to put the

00:15:45

date in the first column we can also

00:15:47

swap so here we will also put

00:15:50

delete

00:15:54

column

00:16:01

least year

00:16:07

here we will we can put here we will

00:16:10

put here allowed

00:16:18

and then there will only be one

00:16:20

last step left that our synthesis and

00:16:22

our conciliation is finished we will be

00:16:24

able to ask it here to format

00:16:27

all the fields correctly so for that then

00:16:29

either I detect all the columns

00:16:31

there and I'm going to tell him here in

00:16:34

transform you have a button which

00:16:36

allows you to detect the data types

00:16:37

so there he did he put a date he

00:16:41

put a text he discovered that

00:16:43

it was text and there he said that

00:16:45

it was a figure

00:16:57

type data

00:17:02

here so we have here now

00:17:03

finalized our query if we want to

00:17:05

visualize it you can go to

00:17:06

the Home tab advanced editor and here

00:17:11

you can tell him to show us

00:17:14

the whole query this is what allows you to

00:17:16

create here with here the English structures

00:17:21

so here we have all the requests

00:17:23

which allows us to be able to take the steps which

00:17:26

allow us to

00:17:27

combine merge and consolidate the

00:17:31

files ok so from there

00:17:33

it's over so we could say

00:17:34

in the Home tab closed loaded in

00:17:36

and there we could tell it to put that

00:17:38

in a new sheet in a

00:17:40

table in a new sheet we

00:17:42

do OK and there you will see it will

00:17:44

generate the consolidation with all my

00:17:47

data

00:17:48

inside

00:17:52

so to convince you that this is

00:17:55

automatic okay he has just changed

00:17:57

495 lines loaded directly so

00:18:01

let's admit that now I come back to

00:18:03

my workbook

00:18:09

where my file is located here so here if

00:18:12

I go back to my documents

00:18:14

in here in the exercises in power

00:18:19

pivot

00:18:21

in source and then inside we

00:18:23

are going to look for the file

00:18:24

let's admit here that I duplicate this file

00:18:31

so we are going to make a copy and we are going to

00:18:33

make a collar

00:18:36

ok we are going to rename it

00:18:39

okay we are going to call it here in the month

00:18:42

of April

00:18:48

for this month of April we are going to open it

00:18:56

let's admit that for its month of April

00:18:58

here we are going to create a new

00:19:00

sheet which is called the new store

00:19:03

OK

00:19:05

with here a few more rules

00:19:09

here

00:19:11

we are going to save

00:19:14

we are going to close

00:19:15

and now I am going to tell it in the

00:19:18

query right click

00:19:20

current

00:19:21

so there it has just updated it

00:19:24

has just retrieved the month of April we are going to

00:19:25

check but we have here the month

00:19:27

of April which has just arrived with here my

00:19:30

reference 9 which has also just been

00:19:32

recovered from this umpteenth binder

00:19:36

so here we have just created

00:19:37

a consolidation which makes it possible to

00:19:39

consolidate several binders

00:19:42

located in a folder and in this

00:19:43

binder in each of these classes for

00:19:45

have several sheets also

00:19:48

here is the query which allows you to

00:19:51

combine and consolidate to merge

00:19:55

workbooks and sheets come on I

00:19:57

will stop there so here is how we

00:19:59

consolidate

00:20:01

here workbooks

00:20:06

containing several sheets come on

00:20:07

I hope this tutorial will help you I liked it and I'll

00:20:09

see you soon on a next

00:20:10

tutorial, goodbye

Description:

Bonjour, Dans cette vidéo vous apprendre comment combiner tous les classeurs d'un dossier ayant chacun plusieurs feuilles. Ce type de consolidation est très souvent utilisée, vous allez voir comment réaliser cette dernière grâce à une requête Power Query Pour se former aux outils bureautiques Excel, vous pouvez consulter ma boutique : https://learnaccess.kneo.me/shop En vous souhaitant un bon visionnage. Learnaccess Formation. ********************************************************************************************************* Utiliser mon lien de parrainage pour acheter un produit Tesla et obtenir des crédits gratuits : - kilomètres gratuits en Superchargeur - des vêtements et accessoires. https://ts.la/jeanpaul673722 *************************************************************************************************************

Preparing download options

Popular

HD video

Only sound

All

* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."

** — Link intended for online playback in specialized players

Questions about downloading video

How can I download "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement." video?

http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.
The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.
UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.
UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.

Which format of "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement." video should I choose?

The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.

Why does my computer freeze when loading a "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement." video?

The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.

How can I download "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement." video to my phone?

You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.

How can I download an audio track (music) to MP3 "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement."?

The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.

How can I save a frame from a video "Power Query - Comment Combiner des Classeurs et Feuilles automatiquement."?

This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.

What's the price of all this stuff?

It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.