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

[Kiwi_911]

Hello everyone,  I'm loading data from a data folder. In it, there is one file per organizational unit, whose row structure (= key figures) is always identical. In the columns are the respective values of the area and the organisational sub-units . That means the rows are structured identically, the number of columns varies.

Example:

Kennzahl	Definition	Bereich ABC	Abteilung HGF	Abteilung ONM
Kennzahl 1	Beschreibung KnZ 1	4250	750	3500
Kennzahl 2	Beschreibung KnZ 2	475	300	175
Kennzahl 3	Beschreibung KnZ 3	41%	5%	41%
Kennzahl 4	Beschreibung KnZ 4	1,2	0,4	0,8

<b>Datei 2</b>

Kennzahl	Definition	Bereich GHI	Abteilung QRS	Abteilung UVW
Kennzahl 1	Beschreibung KnZ 1	2500	500	2000
Kennzahl 2	Beschreibung KnZ 2	530	150	380
Kennzahl 3	Beschreibung KnZ 3	32%	12%	25%
Kennzahl 4	Beschreibung KnZ 4	1,5	0,6	0,9

Taget:

Kennzahl	Definition	Bereich ABC	Abteilung HGF	Abteilung ONM	Bereich GHI	Abteilung QRS	Abteilung UVW
Kennzahl 1	Beschreibung KnZ 1	4250	750	3500	2500	500	2000
Kennzahl 2	Beschreibung KnZ 2	475	300	175	530	150	380
Kennzahl 3	Beschreibung KnZ 3	41%	5%	41%	32%	12%	25%
Kennzahl 4	Beschreibung KnZ 4	1,2	0,4	0,8	1,5	0,6	0,9

With the ordinary combining, the files are put below each other. I have already read a lot and tried various things, and found and tried variants with using index or pivot, but I have not come to my solution pattern. With the index, my data disappeared during index creation, and afterwards the instructions said "expand data again," but I couldn't manage that. Now I had an index for each metric "Definition", but the other columns were gone. With pivoting, I then had all the data duplicated in rows and columns. I just can't find a clear solution. And I really also need a step-by-step explanation or the complete M-Code to understand, because I need to solve this problem in multiple requirements.

Many thanks for your help and best regards,

Kiwi

In German:

Hallo zusammen,

ich habe folgendes Anliegen:

Ich rufe die Daten aus einem Ordner ab, Darin befinden sich je Organisationsbereich eine Dateien, deren Zeilenaufbau (= Kennzahlen) immer identisch ist. In den Spalten sind die jeweiligen Werte des Bereichs und der zum Bereich gehörenden Abteilungen. Das heißt die Zeilen sind identisch aufgebaut, die Anzahl der Spalten variiert.

Hier ein Muster:

<b>Datei 1</b>
Kennzahl Definition Bereich ABC Abteilung HGF Abteilung ONM
Kennzahl 1 Beschreibung KnZ 1 4250 750 3500
Kennzahl 2 Beschreibung KnZ 2 475 300 175
Kennzahl 3 Beschreibung KnZ 3 41% 5% 41%
Kennzahl 4 Beschreibung KnZ 4 1,2 0,4 0,8

<b>Datei 2</b>
Kennzahl Definition Bereich GHI Abteilung QRS Abteilung UVW
Kennzahl 1 Beschreibung KnZ 1 2500 500 2000
Kennzahl 2 Beschreibung KnZ 2 530 150 380
Kennzahl 3 Beschreibung KnZ 3 32% 12% 25%
Kennzahl 4 Beschreibung KnZ 4 1,5 0,6 0,9

Daraus möchte ich nun eine Gesamtdatei erstellen in der Form, dass die ersten beiden Spalten "Kennzahl" und "Definition" bestehen bleiben und die Spalten "Bereich..." und "Abteilung..." nebeneinandergestellt werden:

Ziel:
Kennzahl Definition Bereich ABC Abteilung HGF Abteilung ONM Bereich GHI Abteilung QRS Abteilung UVW
Kennzahl 1 Beschreibung KnZ 1 4250 750 3500 2500 500 2000
Kennzahl 2 Beschreibung KnZ 2 475 300 175 530 150 380
Kennzahl 3 Beschreibung KnZ 3 41% 5% 41% 32% 12% 25%
Kennzahl 4 Beschreibung KnZ 4 1,2 0,4 0,8 1,5 0,6 0,9

Beim "herkömmlichen" Kombinieren, werden die Dateien untereinandergehängt. Ich habe schon viel nachgelesen und probiert und Varianten mit Index und Spalte pivotieren gefunden und ausprobiert, aber bin nicht zu meinem Lösungsmuster gekommen. Bei Index verschwanden meine Daten bei der Indexbildung, danach hieß es in der Anleitung "Daten wieder expandieren" aber ich habe das nicht hinbekommen. Nun hatte ich zwar je Kennzahl einen Index aber die anderen Spalten waren weg. Beim Pivotieren habe ich anschließend alle Daten mehrfach in Zeilen und Spalten enthalten. Ich finde einfach keine saubere Lösung. Und ich brauche auch wirklich eine Schritt-für-Schritt-Erklärung oder die vollständige Darstellung der Umsetzung im Erweiterten Editor um es zu Verstehen, denn dieses Problem muss ich in mehreren Anforderungen lösen.

Vielen lieben Dank für eure Hilfe und Gruß,

KiWi

[Zanqueta · Accepted solution]

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

[Kiwi_911]

Thanks so much for your help, Zanqueta. It works!

[Hans-Georg_Puls · Accepted solution]

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

[Kiwi_911]

Hello Hans-Georg, thank you very much for your help. One correction, in line "Promoted Headers" I had to add a "," at the end. It works! Perfekt, thanks! 

[Zanqueta · Accepted solution]

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