Put columns below other columns in same table

[wj14]

Hi,

I'm trying to fix the following thing in a Transform Sample File (that should do the same steps to all files in a folder):

I have a bunch of Excel files with multiple columns that I want below eachother (like appending, but that isn't working because when I use duplicated query of the transform sample file, it won't work for all the other files in the folder).

I have twelve columns:

Column 1, 5 and 9 are all the values of the schools

Column 2, 6 and 10 are the coursenames

Column 3, 7 and 11 are the coursecodes

Column 4, 8 and 12 are the results.

I want column 1,5 and 9 below eachother, the same for the other group of columns. Is there a way to do this in Power Query?

** The example I am using is "fake" data, but the same structure as my real problem.

I have an Excel file that looks like below (Current situation), that I load into Power BI. So I want a solution in Power BI to get the desired situation below.

Current Situation

Desired situation

Hopefully someone can help.

Thanks

[johnbasha33]

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