Power Query Load from data folder - data needed merged side by side not appended

Hello Kiwi_911 , in this case, it is important to understand that the Folder Combine function in Power Query works by default as an Append (stacking rows), while the requirement is to merge the data side by side.

The files need to be merged column-wise based on the common keys:
Kennzahl
Definition
This is not an Append scenario, but a Merge (Join) scenario across multiple files.
When using:
Home → Get Data → Folder → Combine
Power Query assumes that all files have the same structure and should be appended. However, in this situation:
The rows are identical across all files (Kennzahl)
The columns vary between files (Organisational Units)
The correct approach is to append the files temporarily and then pivot the organisational columns in order to convert the rows back into columns.

Try this:

 

let
Source = Folder.Files("C:\YourFolderPath"),
FilteredFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
GetData = Table.AddColumn(FilteredFiles, "Data", each Excel.Workbook([Content], true)),
ExpandSheets = Table.ExpandTableColumn(GetData, "Data", {"Data"}),
ExpandData = Table.ExpandTableColumn(ExpandSheets, "Data", {"Kennzahl","Definition","Column3","Column4","Column5"}),
Unpivoted = Table.UnpivotOtherColumns(ExpandData, {"Kennzahl","Definition"}, "Attribute", "Value"),
Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value")
in
Pivoted

Official Microsoft documentation:
Unpivot columns - Power Query | Microsoft Learn

 

Hi Kiwi_911 ,

another option is to do two transpose operations, one for every single file and one for the final combined file, and wait with promoting the headers until the end of the process.

Assuming that you have a basic "Load from folder" setup,

you have to do the following:

1. Change your "Transform Sample File" query to this:
   let
   Source = Excel.Workbook(Parameter1, null, true),
   Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
   #"Transposed Table" = Table.Transpose(Tabelle1_Sheet)
   in
   #"Transposed Table"
   Important is that you don't do any header promoting but transpose your table
2. Change your input query to this:
   let
   Source = Folder.Files("D:\FamilyCloud\Arbeit\Hase\2016_Freelancer\Portfolio\Demos\ExcelFolderInput\Input"),
   #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
   #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
   #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
   #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
   #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
   #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
   #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}),
   #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Column1"}),
   #"Transposed Table" = Table.Transpose(#"Removed Duplicates"),
   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Kennzahl", type text}, {"Definition", type text}, {"Bereich ABC", type number}, {"Abteilung HGF", type number}, {"Abteilung ONM", type number}, {"Bereich GHI", type number}, {"Abteilung QRS", type number}, {"Abteilung UVW", type number}})
   in
   #"Changed Type"
    
   1. Removed Columns removes the source file names
   2. Removed Duplicates removes the duplicate Kennzahl definition rows
   3. Transposed Table gives the table the desired layout
   4. Promoted Headers sets finally the column names

Hope that helps. If you need more information or my demo file, please let me know...