Transposing table with null values

[Anonymous]

Is it possible to achive transposing the table like below with null values? I've tried clicking on "Transpose" in Power query and it's not transforming the way I want.. any help would be appreciated!! 

DeptID	Directors_math	Directors_sci
111	null	Haley
111	null	Jamie
111	null	Lee
222	AmyK	Jenny
222	AmyK	Sam

[wdx223_Daniel]

= let a=Table.ColumnNames(Source) in Table.Combine(Table.Group(Source,"DeptID ",{"n",each #table({a{0}}&List.TransformMany(List.Skip(a),(x)=>List.Positions([#"DeptID "]),(x,y)=>x&"_"&Text.From(y+1)),{{[#"DeptID "]{0}}&List.Combine(List.Skip(Table.ToColumns(_)))})})[n])

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can try this. I have attached a sample file at bottom. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRyivNyQFSHok5qZVKsTpowl6JuZmpmMI+qRBBIyMjIM8xt9IbpDY1L68SUzg4MVcpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DeptID " = _t, Directors_math = _t, Directors_sci = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DeptID ", Int64.Type}, {"Directors_math", type text}, {"Directors_sci", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DeptID "}, {{"Directors_math", each [Directors_math], type nullable list}, {"Directors_sci", each [Directors_sci], type nullable list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Directors_math", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Directors_sci", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Directors_math", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Directors_math.1", "Directors_math.2", "Directors_math.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Directors_math.1", type text}, {"Directors_math.2", type text}, {"Directors_math.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Directors_sci", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Directors_sci.1", "Directors_sci.2", "Directors_sci.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Directors_sci.1", type text}, {"Directors_sci.2", type text}, {"Directors_sci.3", type text}})
in
    #"Changed Type2"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!