Forum Discussion

SteelerFan's avatar
SteelerFan
Frequent Visitor
2 years ago

Split a single row into multiple rows based on multiple columns

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!

Need Help

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

  • SteelerFan's avatar
    SteelerFan
    Frequent Visitor
    2 years ago

    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