Power Query - Pivoting without gaps

Question

Sorry, I could not think of a better, fitting title. The text should clarify ...

In thefollowing scenario: 4 employees should work on different days. The table looks like this:

Date        Name
12/13/2018  Carol, John
12/14/2021  Peter, Carol, John, Alice
12/15/2018  Alice
12/16/2018  Peter, Alice
12/17/2018  John, Peter

OK, split names at "," (with trailing space) and paste as lines; since there must be 3 columns: insert index column and then pivoting brings this result:

Index     12/13/2018 12/14/2018 12/15/2018 12/16/2018 12/17/2018
      0   Carol      null       null       null       null
      1   John       null       null       null       null
      2   null       Peter      null       null       null
      3   null       Carol      null       null       null
      4   null       John       null       null       null
      5   null       Alice      null       null       null
      6   null       null       Alice      null       null
      7   null       null       null       Peter      null
      8   null       null       null       Alice      null
      9   null       null       null       null       John
     10   null       null       null       null       Peter

But what I want as a result:

Index  12/13/2018  12/14/2018  12/15/2018  12/16/2018  12/17/2018
   0   Carol       Peter       Alice       Peter       John
   1   John        Carol       null        Alice       Peter
   2   null        John        null        null        null
   3   null        Alice       null        null        null

I will delete the column index later. So I got to the desired goal: with the exception of the column index and the (correctly arranged) column 12/13/2018 create each additional column as a new query and filter / delete the null cells. Then delete the previous date column and re-insert the newly created date column into the query via merge.

This is very cumbersome and tedious in my opinion. Is there a better way in Power Query?

I would be glad, if you would describe a better way with simple words and if possible step by step (not only M-Code).

horseyride horseyride · Accepted Answer · 2018-12-15T19:42:53

Take the table, split by delimiter (commas,each occurance), select then transpose entire table, add index

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"List", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3", "List.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List.1", type text}, {"List.2", type text}, {"List.3", type text}, {"List.4", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column1", "Column2", "Column3", "Column4", "Column5"})
in  #"Reordered Columns"

Power Query - Pivoting without gaps

1 Answers