Fausia laulau e eseese ulutala mai tusi e tele

Fa'atulagaina o le fa'afitauli

We have several files (in our example – 4 pieces, in the general case – as many as you like) in one folder lipoti:

Inside, these files look like this:

Lea:

• The data sheet we need is always called ata, but can be anywhere in the workbook.
  
• Beyond the sheet ata Each book may have other sheets.
• Tables with data have a different number of rows and may start with a different row on the worksheet.
• The names of the same columns in different tables may differ (for example, Quantity = Quantity = Qty).
• Columns in tables can be arranged in a different order.

Task: collect sales data from all files from the sheet ata into one common table in order to subsequently build a summary or any other analytics on it.

Step 1. Preparing a directory of column names

The first thing to do is to prepare a reference book with all possible options for column names and their correct interpretation:

We convert this list into a dynamic “smart” table using the Format as table button on the tab Itulau Amata (Aiga — Fa'atulagaina e pei o le Laulau) po'o le ala pupuu keyboard ctrl+T and load it into Power Query with the command Faʻamatalaga - Mai Laulau / Vaʻaiga (Fa'amatalaga — Mai le Laulau/Fa'asologa). In recent versions of Excel, it has been renamed to Faatasi ai ma laulaau (Mai le laupepa).

In the Power Query query editor window, we traditionally delete the step Suia Ituaiga and add a new step instead of it by clicking on the button f_xin the formula bar (if it is not visible, then you can enable it on the tab toe iloilo) and enter the formula there in the built-in Power Query language M:

=Table.ToRows(Source)

This command will convert the one loaded in the previous step puna reference table into a list consisting of nested lists (List), each of which, in turn, is a pair of values It was-became from one line:

We will need this type of data a little later, when mass renaming headers from all loaded tables.

After completing the conversion, select the commands Fale — Tapuni ma uta — Tapuni ma uta i… and type of import Na'o le faia o se feso'ota'iga (Aiga — Tapuni&Uta — Tapuni&Uta i… — Na'o le faia o fesootaiga) and go back to Excel.

Step 2. We load everything from all files as is

Now let’s load the contents of all our files from the folder – for now, as is. Choosing teams Faʻamatalaga - Maua faʻamatalaga - Mai le faila - Mai le faila (Faʻamatalaga - Maua Faʻamatalaga - Mai le faila - Mai le faila) and then the folder where our source books are.

In the preview window, click Suʻe (Suiga) or liuga (Faʻatonutonu):

And then expand the contents of all downloaded files (Binary) button with double arrows in the column heading lotomalie:

Power Query on the example of the first file (Vostok.xlsx) will ask us the name of the sheet we want to take from each workbook – choose ata and press OK:

After that (in fact), several events that are not obvious to the user will occur, the consequences of which are clearly visible in the left panel:

1. Power Query will take the first file from the folder (we will have it Vostok.xlsx — vaʻai File example) as an example and imports its content by creating a query Convert sample file. This query will have some simple steps like puna (file access) tuʻuvaʻaga (sheet selection) and possibly raising the titles. This request can only load data from one specific file Vostok.xlsx.
   
2. Based on this request, the function associated with it will be created Convert file (indicated by a characteristic icon f_x), where the source file will no longer be a constant, but a variable value – a parameter. Thus, this function can extract data from any book that we slip into it as an argument.
   
3. The function will be applied in turn to each file (Binary) from the column lotomalie – step is responsible for this Valaau galuega masani in our query that adds a column to the list of files Convert file with import results from each workbook:

   

4. Extra columns are removed.
5. The contents of nested tables are expanded (step Extended table column) – and we see the final results of data collection from all books:

   
   

Step 3. Sanding

The previous screenshot clearly shows that the direct assembly “as is” turned out to be of poor quality:

• The columns are reversed.
• Many extra lines (empty and not only).
• Table headers are not perceived as headers and are mixed with data.
  

You can fix all these problems very easily – just tweak the Convert Sample File query. All adjustments that we make to it will automatically fall into the associated Convert file function, which means they will be used later when importing data from each file.

By opening a request Convert sample file, add steps to filter unnecessary rows (for example, by column Column2) and raising the headings with the button Fa'aoga laina muamua e fai ma ulutala (Fa'aoga le laina muamua e fai ma ulutala). The table will look much better.

In order for columns from different files to automatically fit under each other later, they must be named the same. You can perform such a mass renaming according to a previously created directory with one line of M-code. Let’s press the button again f_x in the formula bar and add a function to change:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

This function takes the table from the previous step Ulutala si'i and renames all columns in it according to the nested lookup list Ulutala. Third argument MissingField.Ignore is needed so that on those headings that are in the directory, but are not in the table, an error does not occur.

Actually, that’s all.

Returning to the request lipoti we will see a completely different picture – much nicer than the previous one:

• What is Power Query, Power Pivot, Power BI and why an Excel user needs them
  
• Aoina mai faʻamatalaga mai faila uma i totonu o se faila
• Aoina fa'amaumauga mai pepa uma o le tusi ile laulau e tasi