Put columns below other columns in same table

wj14 

To achieve the desired situation where the columns are stacked vertically in Power Query (Power BI), you can use the following steps:

1. Load your Excel files into Power Query. You can do this by using the "Get Data" option in Power BI and selecting "Folder" as your data source. This will allow you to import all files from a specific folder.

2. Combine all the files into a single query. After loading files from the folder, you'll see a list of files. Click on "Combine" -> "Combine & Load" to combine them into a single query.

3. Split the columns into groups. You'll have 12 columns in total, split them into 4 groups: schools, course names, course codes, and results. You can achieve this by using the "Split Columns" feature in Power Query.

4. Unpivot each group of columns. For each group (schools, course names, course codes, and results), unpivot the columns so that the values stack vertically. You can do this by selecting the columns, right-clicking, and choosing "Unpivot Other Columns".

5. Merge the unpivoted data back together. Since you have unpivoted each group, you will now have 4 separate tables. Merge these tables together using the "Merge Queries" option in Power Query.

6. Expand the merged table to get the desired structure. After merging, you'll have a single table with all values stacked vertically. Expand the columns as needed to get the desired structure.

Here's a rough outline of the steps in Power Query:

let
// Load files from folder
Source = Folder.Files("C:\Your\Folder\Path"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx" or [Extension] = ".xls")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Name", "Data"}, {"Name", "Data"}),

// Combine all files into a single query
#"Combined Files" = Table.Combine(#"Expanded Data"[Data]),

// Split columns into groups
#"Split Columns" = Table.SplitColumn(#"Combined Files", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"School1", "School2", "School3"}),
// Repeat the above step for other groups (course names, course codes, results)

// Unpivot each group
#"Unpivoted Schools" = Table.UnpivotOtherColumns(#"Split Columns", {"School1", "School2", "School3"}, "Attribute", "Value"),
// Repeat the above step for other groups (course names, course codes, results)

// Merge unpivoted data back together
#"Merged Tables" = Table.Combine({#"Unpivoted Schools", #"Unpivoted Course Names", #"Unpivoted Course Codes", #"Unpivoted Results"}),

// Expand columns as needed
// You may need to rename and reorganize columns as per your desired structure
#"Expanded Merged Tables" = Table.ExpandRecordColumn(#"Merged Tables", "Value", {"Column1", "Column2", "Column3", "Column4"}),
// Repeat the above step for other groups (course names, course codes, results)

in
#"Expanded Merged Tables"

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!