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 offers the opportunity to connect a desired filepath directly, to recieve 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. 

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.

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

Was this helpful?

Yes | Somewhat | No