Power Query: extract data from tables into multiple columns

[cimrman65]

Hi, I have multiple excel files with 3 columns that I want to merge into one sheet. If possible, I would like to have one empty column between each 3 columns. I figured out I would use power query for the import. I got as far as having tables with data in power query, but I need to extract them side by side. First picture is what I have and the second is what I want.

Thank you for all the help!

[#2]

@cimrman65 

In theory that's possible, however it's better to have simple sample file. The only, Power Query returns structured table. Since table can't have repeated headers, they will be like Load1, Load2, etc for each first column, for empty columns it could be Balnk1, Blank2, etc. or so. Could be variants, but in any case each header shall to have it's own unique name.

If you are on Excel 365 perhaps it's better to use formulae and HSTACK all table. Details depends on what are source tables, are they dynamic (number and names) or not.

[#3]

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

[#4]

@cimrman65 

Back to Power Query, as variant.

If some step tables returns set of them like

steps could be

let
   ....
    tables = ....,
    headers = List.Combine(
        List.Transform( tables[Tables],
            (q) => List.Transform( Table.ColumnNames(q) & {"sp"},
                (p) => p & Text.From( List.PositionOf(tables[Tables], q) ) ) ) ),
    columns = List.Combine( List.Transform( tables[Tables], (q) => (Table.ToColumns(q) & {{}}) ) ),
    result = Table.FromColumns( columns, headers )
in
    result

which gives something like