How to Restructure Survey Dataframe in Power BI

Hi Sabr23 

I suggest the following approach. Please note that it works best if there aren’t too many different questions and answers involved, as it could become labor-intensive otherwise. So if we start with the 2025 Werte values :

Step 1: Duplicate the 2025 Werte column.

Step 2: Add a custom column called Age Question, using the following logic (adjust the wording as needed):

if Text.Contains([Frage 22], "old", Comparer.OrdinalIgnoreCase) or Text.Contains([Frage 22], "Years", Comparer.OrdinalIgnoreCase) then "How Old are you ?" else null

Step 3: Add another custom column called Gender Question, using this logic (again, adjust wording as needed):

if Text.Contains([Frage 22], "Gender", Comparer.OrdinalIgnoreCase) 
    or Text.Contains([Frage 22], "Male", Comparer.OrdinalIgnoreCase)
    or Text.Contains([Frage 22], "Female", Comparer.OrdinalIgnoreCase)
    or Text.Contains([Frage 22], "Other", Comparer.OrdinalIgnoreCase)
    or Text.Contains([Frage 22], "No Information", Comparer.OrdinalIgnoreCase)
then "What is your gender ? " 
else null

At this point you have something like this :

Step 4: Replace all null values in the Age Question and Gender Question fields with "No Value".

Step 5: Duplicate the 2025 Werte field again to create a new field called 2025 Werte - Copy.

Step 6: Select the Age Question column, go to Transform > Pivot Column, and set 2025 Werte as the values column.

Step 7: Then select the Gender Question column, go to Transform > Pivot Column, and set 2025 Werte - Copy as the values column.

Step 8: Remove and clean up the columns as required

You should then go from this for 2025 Werte (  as per before step 1 )  :

to this which is hopefully close to the logic you require :

and by using filters in your dashboard you can select different information for different views :

Step 9: Repeat the same process for % 2025 Prozent and any other relevant values/questions.

Again as mentioned note that this potentially works best if there aren’t too many different questions and answers involved, as it could become labor-intensive otherwise.

Hope this helps

Antonio

Hi Sabr23 ,

The approach mentiond antfr99 , Power Query conditional logic and pivoting is an excellent method to reshape your survey data for better analysis. Thanks for your response Sabr23 .

1. Add a Custom Column to assign a question label (How old are you) based on keyword matches in the Frage 22 column. This helps distinguish questions from responses.
2. Use Transform  Pivot Column to convert rows into columns, using the labeled question as the pivot key. This restructures your data so each question has its own column.
3. If percentage values are needed alongside absolute numbers, duplicate the necessary columns and apply the same pivoting logic.
4. Remove any nulls or unnecessary rows, then rename columns for clarity to ensure a well structured dataset

Helpful Reference:

Pivot columns feature description - Power Query | Microsoft Learn

Add a custom column - Power Query | Microsoft Learn

By following these steps, you'll get a cleaner, more usable format for survey analysis in Power BI.

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly