Forum Discussion

andrew260z's avatar
andrew260z
Frequent Visitor
5 years ago
Solved

Unpivot multiple columns that may have nulls

I've been trying to figure out way to unpivot data where an issue / priority and status is split on a delimiter. Each item may have 0..n issues attached to it. Thats been represented in a single column making it hard to determine how many sets of columns there may be. So if 1 item has 3 issues then we will end up with 3 sets of 3 columns with values in them. I've tried to follow https://kohera.be/power-bi/how-to-unpivot-twice/ but end up unpivoting and pivoting back to the same data. The end goal is to end up with a tabular list where an item will have issue the issue listed. Would have no rows if issues did not exist.

sample set of data (raw)

 

Item NameIssue(s) 
C-13501-A1-MH-001DP01/P1/Closed Out
C-13501-N1-U-001  
C-13501-N2-D-001DP01/P1/Closed Out;DP02/P1/Closed Out;DP03/P1/Closed Out
C-13501-N3-D-001  
C-13501-N4-U-001DP03/P1/Closed Out

 

Goal data of what one would look like unpivoted.

C-13501-N2-D-001DP01P1Closed Out
C-13501-N2-D-001DP02P1Closed Out
C-13501-N2-D-001DP03P1Closed Out
Need Help
  • Jos_Woolley's avatar
    Jos_Woolley
    5 years ago

    Hi,

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Types" = Table.TransformColumnTypes(Source,{{"Item Name", type text}, {"Issue(s)", type text}}),
    #"Removed Rows with No Issues" = Table.SelectRows(#"Changed Types", each ([#"Issue(s)"] <> null)),
    #"Split by Semicolon" = Table.SplitColumn(#"Removed Rows with No Issues", "Issue(s)", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"Issue(s).1", "Issue(s).2", "Issue(s).3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split by Semicolon", {"Item Name"}, "Attribute", "Value"),
    #"Removed Attribute Column" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split by /" = Table.SplitColumn(#"Removed Attribute Column", "Value", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Issue A", "Issue B", "Issue C"})
in
    #"Split by /"

    Obviously amend the first two lines as required.

    Regards

3 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi,

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Types" = Table.TransformColumnTypes(Source,{{"Item Name", type text}, {"Issue(s)", type text}}),
    #"Removed Rows with No Issues" = Table.SelectRows(#"Changed Types", each ([#"Issue(s)"] <> null)),
    #"Split by Semicolon" = Table.SplitColumn(#"Removed Rows with No Issues", "Issue(s)", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"Issue(s).1", "Issue(s).2", "Issue(s).3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split by Semicolon", {"Item Name"}, "Attribute", "Value"),
    #"Removed Attribute Column" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split by /" = Table.SplitColumn(#"Removed Attribute Column", "Value", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Issue A", "Issue B", "Issue C"})
in
    #"Split by /"

    Obviously amend the first two lines as required.

    Regards

    • andrew260z's avatar
      andrew260z
      Frequent Visitor
      5 years ago

      Thanks Jos - whilst I couldn't get the MCode to work I was able to step through what you'd done in the PQ Editor GUI and bang on thats what i wanted to achieve.