Split a single row into multiple rows based on multiple columns

[SteelerFan]

Hi,

I 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!

[SteelerFan]

In a reply from the EDNA forum this solution worked for me, hopefully it will help others.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMhhIBARHKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"College 2" = _t, #"Dept 2" = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"PIDM", Int64.Type}, {"College 1", type text}, {"Dept 1", type text}, {"College 2", type text}, {"Dept 2", type text}}),

#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"College 1", "Dept 1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-1"),

#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"College 2", "Dept 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-2"),

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"PIDM"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "College"}, {"Value.2", "Dept"}})

in #"Renamed Columns"

Thanks

[wdx223_Daniel]

=#table({"PIDM","College","VP"},List.TransformMany(Table.ToRows(YourTable),each List.Split(List.Skip(_),2),(x,y)=>{x{0}}&y))