Split a single row into multiple rows based on multiple colums

Power Query
1

Hi,

I’m new to Power BI and new to this board. I have an incoming table like this:

PIDM College 1 Dept 1 College2 Dept2
1 VP ART AS PSY

My Final table should look like this:

PIDM College Dept
1 VP ART
1 AS PSY

I’ve tried using unpivot but the resulting table is

PIDM College1 VP
Dept1 ART
College2 AS
Dept 2 PSY

Any help would be greatly appreciated.

Thanks so much!
Dan

2

This is my solution with UI:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMiQSRAYDCVc/dyDp4RmsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"Dept 2" = _t, #"College 2" = _t, #"Dept 3" = _t, #"Dept 4" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"PIDM"}, "Attribute", "Dept"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "College", each if Text.Contains([Attribute],"College") then [Dept] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"College"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Attribute], "College")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"PIDM", "College", "Dept"})
in
    #"Removed Other Columns"