How to Restructure Survey Dataframe in Power BI

[Sabr23]

Hi everyone,

I'm new to Power BI and currently working with a survey dataset that I’m struggling to reshape correctly. The data is not in a typical tabular format and I’m unsure how to proceed.

In the current structure, the first column (titled "Frage 22") contains both the questions (e.g., "How old are you?") and their answer categories (e.g., "Under 20", "21–30", "31–40", etc.), all the survey questions and their answer categories are  listed in the same column one after another. To the right, there are  more columns showing the absolute values ("2025 Werte") and the other the percentages ("2025 Prozent") for each answer category.

What I want to do is transform the data so that each survey question has its own column, with the corresponding responses properly aligned, similar to how typical survey results are displayed in Power BI.

Does anyone know how I can go about restructuring this in Power BI (or Power Query)? I would really appreciate any guidance, as I’m currently stuck and not sure how to move forward.

Thank you so much in advance!

[antfr99 · Accepted solution]

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

[V-yubandi-msft]

Hi @Sabr23 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @Sabr23 ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

Thank You.

[V-yubandi-msft · Accepted solution]

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

[antfr99 · Accepted solution]

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

[Sabr23]

Thank you SO much! This was really helpful and it seems working now