Ji gelek pirtûkan bi sernavên cihêreng tabloyan ava bikin

Formulasyona pirsgirêkê

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

Inside, these files look like this:

Ku tê de:

• The data sheet we need is always called Photos, but can be anywhere in the workbook.
  
• Beyond the sheet Photos 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 Photos 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 Xane (Mal - Format wek Tablo) an jî kurteya klavyeyê Ctrl+T and load it into Power Query with the command Daneyên - Ji Tablo / Rêze (Daneyên - Ji Tablo/Range). In recent versions of Excel, it has been renamed to Bi pelan (Ji pelê).

In the Power Query query editor window, we traditionally delete the step Tîpa guherî 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 Axaftin) 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 Kanî 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 Mal - Girtin û Barkirin - Girtin û Li… and type of import Tenê pêwendiyek çêbikin (Mal - Girtin & Barkirin - Girtin & Barkirin... - Tenê pêwendiyê biafirînin) 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 Dane - Daneyan bistînin - Ji pelê - Ji peldankê (Daneyên - Daneyan bistînin - Ji pelê - Ji peldankê) and then the folder where our source books are.

In the preview window, click Gûhertin (Guherandin) or Gûherrandinî (Weşandin):

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

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 Photos 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 — dîtin File example) as an example and imports its content by creating a query Convert sample file. This query will have some simple steps like Kanî (file access) Navigation (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 Dilşad – step is responsible for this Banga fonksiyona xwerû 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 Rêza yekem wekî sernav bikar bînin (Rêza yekem wekî sernav bikar bînin). 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 Elevated headers and renames all columns in it according to the nested lookup list Headlines. 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 Reports 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
  
• Collecting data from all files in a given folder
• Daneyên ji hemî pelên pirtûkê di yek tabloyê de berhev dike