Power Query: extract data from tables into multiple columns

cimrman65 

I'd be happy to help you merge your multiple Excel files and extract the data into separate columns with Power Query. To achieve this, you'll need to employ a few steps:

1. Combine the files:

There are two ways to do this:

• Using "Append" function:

  • Select each table you want to merge in the "Queries" pane.
  • Right-click one of them and choose "Append."
  • In the "Append Queries" dialog, choose the appropriate column to join the tables on (usually a shared identifier like ID or name).
  • Click "OK" to combine the tables.

• Using "Table.Combine" function:

  • Go to the "Home" tab in Power Query Editor.
  • Click "Add Data" > "From Folder."
  • Select the folder containing your Excel files.
  • Choose "Combine Files" and select "Combine files with a common delimiter."
  • Leave the delimiter blank if there isn't one.
  • Click "Next" and choose the appropriate column to join the tables on.
  • Click "Finish" to combine the tables.

2. Extract data into separate columns:

• Identify the columns you want to extract in each "group" of three.
• For each group, use the "Table.ColumnFromList" function. This function converts a list of values into a new column.
• For example, if you want to extract columns 1, 2, and 3 from the first group, you can use the following formula:
• #"YourCombinedTable" = Table.AddColumn(#"YourCombinedTable", "Column1", (row) => row[1]),
• #"YourCombinedTable" = Table.AddColumn(#"YourCombinedTable", "Column2", (row) => row[2]),
• #"YourCombinedTable" = Table.AddColumn(#"YourCombinedTable", "Column3", (row) => row[3])

• Repeat this process for each group of three columns you want to extract.

3. Add empty columns:

• Use the "Insert Column" function to insert empty columns between each group of three extracted columns.
• For example, you can use the following formula to insert an empty column after "Column3":

#"YourCombinedTable" = Table.InsertColumn(#"YourCombinedTable", "Empty1", List.Repeat(null, Table.RowCount(#"YourCombinedTable")), Int64.From(4))

• Adjust the formula to insert as many empty columns as you need and at the desired positions.

4. Rename columns:

• Rename the extracted and empty columns for clarity. You can do this directly in the "Name" column in the "Query Settings" pane.

Remember to replace "YourCombinedTable" with the actual name of your table and adjust the formulas based on your specific column positions and desired layout.

By following these steps, you should be able to merge your Excel files and extract the data into separate columns with empty columns in between, achieving the desired layout you shared in the pictures.