Survey data from Microsoft form

[HaiVN]

Hello , 

I have data set from Microsoft form which as follow:

I want to transform to as follow:

The Ideas is to ananlyse which are the "education need" and how many personers , degrees of each and when company needs personers.

I plan to copy from 1 to 2 data sets, delete the column D;E,F,G at the data set number 2, and join 2 data sets together? 
Anyone has any idea better to solve this? 

Thank you! 

[v-hashadapu]

Hi @HaiVN , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[v-hashadapu]

Hi @HaiVN , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[Omid_Motamedise]

Hi @HaiVN 

It is a really interesting question, and I will create a video for it.

Now, you can use the following code, including data and result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSUXJ0cgaRQOwZAiSMgdgvvxxIOgUng8jM/Jz89EqoTGaegpFCZGpiUTGQ45tYXJJaBGIoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, City = _t, #"Type of Bussiness" = _t, #"Education need 1" = _t, #"Total 1" = _t, #"When 1" = _t, #"Degree 1" = _t, #"Education need 2" = _t, #"Total 2" = _t, #"When 2" = _t, #"Degree 2" = _t, Task = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"Education need 1", "Total 1", "When 1", "Degree 1", "Education need 2", "Total 2", "When 2", "Degree 2"},each List.Split(_,4),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    Custom1 = Table.SplitColumn(#"Expanded Merged","Merged", each _,4)
in
    Custom1

[Royel]

HI @HaiVN just change the source with your actual table. 

Here is complete m-code 

let
    Source = Table.FromRows(
        {
            {1, "Project A", "Hanoi", "Manufacturing", "Data Science", 5, "Q1 2025", "Master", "AI/ML", 3, "Q2 2025", "PhD", "Cybersecurity", 2, "Q3 2025", "Bachelor"}
        },
        type table [
            ID = Int64.Type, Task = text, city = text, #"Type of Business" = text, 
            #"Education need 1" = text, #"Total 1" = Int64.Type, #"When 1" = text, #"Degree 1" = text,
            #"Education need 2" = text, #"Total 2" = Int64.Type, #"When 2" = text, #"Degree 2" = text,
            #"Education need 3" = text, #"Total 3" = Int64.Type, #"When 3" = text, #"Degree 3" = text
        ]
    ),
    
    FixedColumns = {"ID", "Task", "city", "Type of Business"},
    Unpivoted = Table.UnpivotOtherColumns(Source, FixedColumns, "Attribute", "Value"),
    AddBaseName = Table.AddColumn(Unpivoted, "BaseName", 
        each Text.TrimEnd(Text.BeforeDelimiter([Attribute], " ", {0, RelativePosition.FromEnd}), {" "})),
    AddGroupNum = Table.AddColumn(AddBaseName, "GroupNum", 
        each Text.AfterDelimiter([Attribute], " ", {0, RelativePosition.FromEnd})),
    Filtered = Table.SelectRows(AddGroupNum, each [Value] <> null and [Value] <> ""),
    Cleaned = Table.RemoveColumns(Filtered, {"Attribute"}),
    Pivoted = Table.Pivot(Cleaned, List.Distinct(Cleaned[BaseName]), "BaseName", "Value"),
    Final = Table.RemoveColumns(Pivoted, {"GroupNum"})
in
    Final

Thanks 

[v-hashadapu]

Hi @HaiVN , Thank you for reaching out to the Microsoft Community Forum.

I normalized the form output in Power Query so each education need becomes a separate row instead of duplicating the dataset. This makes the model scalable and suitable for analysis by education type, degree, and timing. The transformation uses unpivot/pivot logic and requires no manual maintenance if more education needs are added.

To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

[ronrsnfld]

You should compare your approach with this:

Unpivot the data

Remove the trailing "spaces" and digits from the resultant Attributes column.

Pivot the Attributes column, using a custom function to avoid the usual errors seen when you have multiple entries per aggregation:

Custom Pivot Function

Rename as seen in the code

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Main Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID","Task"}, "Attribute", "Value"),
    #"Remove Trailing Digits and Spaces" = Table.TransformColumns(#"Unpivoted Other Columns",
        {"Attribute", each Text.TrimEnd(_,{"0".."9"," "}), type text}),
    #"Pivot No Aggregation" = fnPivotAll(#"Remove Trailing Digits and Spaces","Attribute","Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivot No Aggregation",{
        {"ID", Int64.Type}, {"Task", type text}, {"city", type text}, {"Type of Business", type text}, 
        {"Education need", type text}, {"Total", Int64.Type}, {"When", type text}, {"Degree", type text}})
in
    #"Changed Type"

Results from your data: