Restructuring Data in Excel using Power Query

On this page we will be showing you an easy way of formatting an Excel Document to fit the guidelines for data used in Valsight.


Data sources uploaded to Valsight need to be correctly formatted as shown in the picture below.


If you have an Excel Document that is not following those guidelines (e.g. the document in the picture below), there is an easy way of changing it into the correct format using Excel Power Query.


There are different possibilites and reasons to work with Power Query:

Import Data into a new Excel Document



Create an empty Excel document and select 'Data' → 'Get Data'  → 'From File' → 'Excel Worksheet'. Now select the file that contains the desired data.


Excel automatically imports the data. Now select 'Clear Data' to change the data structure in the Power Query-Editor.



Erstellen Sie ein leeres Excel Dokument und wählen Sie 'Daten' → 'Daten abrufen'  → 'Aus Datei' → 'Aus Arbeitsmappe'. Wählen Sie nun die Datei aus der die Daten importiert werden sollen.


Excel importiert die Daten nun automatisch aus der Arbeitsmappe. Wählen Sie nun 'Bearbeiten' aus, um die Datenstruktur zu bearbeiten.


Changing the Data Structure 



In this case select 'Use First Row As Headers' to properly title the columns. In general use the suitable tool for your need offered by the Power Query-Editor (e.g. "Remove Rows/Columns", "Split Column", …) to transform your table.


The next step is to select all the columns containing data. In this case this means selecting the columns '2017', '2018', '2019' and '2020'. 
Afterwards click 'Transform' → 'Unpivot Columns'. 


Now the only thing left to do is renaming the columns by double clicking on their title and to 'close & load'. 


Congratulations, you have successfully restructured your data. The resulting worksheet is now linked to the original document.


In unserem Beispiel nutzen wir die Schaltfläche 'Erste Zeile als Überschriften verwenden', um die Spalten ordnungsgemäß zu betiteln. Sie können nun die verfügbaren Power Query-Tools (z.B. Zeilen/Spalten entfernen, Spalte Teilen, ...) nutzen, um die Tabelle anzupassen.


Als nächstes werden alle Spalten ausgewählt, die Daten enthalten. In unserem Beispiel wählen wir die die Spalten '2017', '2018', '2019' und '2020'. Wählen Sie anschließend 'Transformieren' → 'Spalten entpivotieren' aus. 


Jetzt müssen nur noch die Spalten umbenannt werden. Dies kann einfach über einen Doppelklick auf den Titel getan werden. Anschließend können die Änderungen über 'Schließen & laden' gespeichert werden.


Glückwunsch, Sie haben Ihre Daten erfolgreich umstrukturiert. Das neue Excel Dokument ist außerdem mit dem original Dokument verknüpft.


Import Data via direct filepath


Power Query offers the opportunity to connect a desired filepath directly, to receive the matching data.


First task is to create a table with one column and one row. You have to name the column (e.g. "Product") and the table (e.g. "Tabl") to reference them later.

Now you write down the filepath, which leads to the desired, data, to the only cell.


Afterwards you open the Power Query-Editor containing the advanced Editor, as seen below.

Select your table and go 'Data' → 'From Table/Range' → 'Home' → 'Advanced Editor'.

There you type down the following comand. Be careful to reference your table and column name correctly, furthermore the sheet reference ({[Item="Tabelle1",..) of the desired data.

--

let

    my_source= Excel.Workbook(File.Contents(Text.From(List.First(Table.Column(Excel.CurrentWorkbook(){[Name="Tabl"]}[Content], "Product")) )), null, true),

    Sheet1_Sheet = my_source{[Item="Tabelle1",Kind="Sheet"]}[Data]

in

    Sheet1_Sheet

--


If you referenced everything correctly, the preview of the data becomes visible.

Just do "Close & Load" to finish the process.


It's done, you created a new sheet which contains the data.

A change or expansion of the referenced data will be adapted automatically. 

Power Query bietet die Möglichkeit, einen gewünschten Dateipfad direkt zu verbinden, um die passenden Daten zu erhalten.


Die erste Aufgabe besteht darin, eine Tabelle mit einer Spalte und einer Zeile zu erstellen. Sie müssen die Spalte (z.B. "Product") und die Tabelle (z.B. "Tabl") benennen, um sie später referenzieren zu können.

Nun notieren Sie den Dateipfad, der zu den gewünschten Daten führt, in die bestimmte Zelle.


Danach öffnen Sie den Power Query-Editor, der den erweiterten Editor enthält, wie unten dargestellt.

Wählen Sie Ihre Tabelle aus und gehen Sie auf 'Datei' → 'Tabelle/Bereich' → 'Home' → 'Erweiterter Editor'

Dort geben Sie den folgenden Befehl ein. Achten Sie darauf, dass Ihr Tabellen- und Spaltenname korrekt referenziert wird, außerdem die Blattreferenz ({[Item="Tabelle1",..) der gewünschten Daten.

--

let

    my_source= Excel.Workbook(File.Contents(Text.From(List.First(Table.Column(Excel.CurrentWorkbook(){[Name="Tabl"]}[Content], "Product")) )), null, true),

    Sheet1_Sheet = my_source{[Item="Tabelle1",Kind="Sheet"]}[Data]

in

    Sheet1_Sheet

--


Wenn Sie alles korrekt referenziert haben, wird die Vorschau der Daten sichtbar.

Führen Sie einfach "Schließen & Laden" aus, um den Prozess zu beenden.


Es ist vollbracht, Sie haben ein neues Blatt erstellt, das die Daten enthält.

Eine Änderung oder Erweiterung der referenzierten Daten wird automatisch angepasst.

Mapping


Let's continue with the given data table. To expand or substitute the given data you can use the "Join" Functions of Power Query.


You can add the additional data in different ways, usually with copy/paste or manually.


Afterwards you convert the data into a second table by selecting the desired data and select 'From Table/Range'.


 Now you close it and import it as a connection only.


By selecting 'Data' → 'Get Data'  → 'Combine Query' → 'Merge' you reach the Merge Window.


There you select for each field one of the given data tables and mark the matching columns.


You can process with the resulting Power Query depending on your needs. To remove the "Productname" information you only need to delete the column.

For more options push the shown icon and choose the information you want to keep.


Now just select 'Close & Load' and your new data table is ready to go.

Fahren wir mit der gegebenen Datentabelle fort. Um die gegebenen Daten zu erweitern oder zu ersetzen, können Sie die "Join"-Funktionen von Power Query verwenden.

Sie können die zusätzlichen Daten auf verschiedene Weise hinzufügen, in der Regel durch Kopieren/Einfügen oder manuell.


Anschließend konvertieren Sie die Daten in eine Zweite Tabelle, indem Sie die gewünschten Daten auswählen und aus 'Tabelle/Bereich' wählen.


 Jetzt schließen Sie ihn und importieren ihn nur noch als Verbindung.


Durch die Auswahl von 'Daten abrufen' → 'Abfragen kombinieren' → 'Zusammenführen' gelangen Sie zum Fenster Zusammenführen.


Dort wählen Sie für jedes Feld eine der vorgegebenen Datentabellen aus und markieren die übereinstimmenden Spalten.


Die resultierende Power Query können Sie je nach Bedarf verarbeiten. Um die Information "Productname" zu entfernen, brauchen Sie nur die Spalte zu löschen.

Für weitere Optionen drücken Sie auf das angezeigte Symbol und wählen Sie die Informationen, die Sie behalten möchten.


Wählen Sie jetzt einfach 'Schließen & Laden' aus. Ihre neue Datentabelle ist nun einsatzbereit.

Was this helpful?

Yes | Somewhat | No