Pivot/Unpivot after delimiter but keep all columns (even those with no data/values)

[baldhillbilly]

Hello all and thanks in advance.   Subject line is a good summary of my problem.    

1)  Have data such as:  

John	a|b|d
Jim	b|e
Jane	a|b|e
Jose	f
Jill	d|f
Joanne	a|b|e|f

2)  Use delimiter to yield:  

	a	b	c	d	e	f
John	a	b	d
Jim	b	e
Jane	a	b	e
Jose	f
Jill	d	f
Joanne	a	b	e	f

3)  Desire to get data in matching column.  I've been able to use some pivot/unpivot options (based on this forum) to get to this:   

	a	b	d	e	f
John	a	b	d
Jim		b		e
Jane	a	b		e
Jose					f
Jill			d		f
Joanne	a	b		e	f

4)  The problem:  Note that column "C" is missing.  I would like to keep it and I'm open to other options as well ..... i.e. reorganizing the data after step 2 to get the data in the matching column header.  

Thanks

Josh

[mahoneypat · Accepted solution]

Here is one way to do it in the query editor.  I would suggest you unpivot your columns, but this gives your desired output I believe.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUUqsSapJUYrVAQpk5gL5STWpEF5iXipUGiqQXwwSSIOqzckBclJqoNz8xDyEcpBgLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Letters = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Letters", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Lists", each Text.Split([Letters], "|")),
    Custom1 = #"Added Custom",
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Name", "Lists"}),
    #"Expanded Lists" = Table.ExpandListColumn(#"Removed Other Columns", "Lists"),
    Custom2 = {"a", "b", "c", "d", "e", "f"},
    #"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Merged Queries" = Table.NestedJoin(#"Converted to Table", {"Column1"}, #"Expanded Lists", {"Lists"}, "Converted to Table", JoinKind.LeftOuter),
    #"Expanded Converted to Table" = Table.ExpandTableColumn(#"Merged Queries", "Converted to Table", {"Name", "Lists"}, {"Name", "Lists"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Converted to Table", List.Distinct(#"Expanded Converted to Table"[Column1]), "Column1", "Lists"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Name] <> null)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "a", "b", "c", "d", "e", "f"})
in
    #"Removed Other Columns1"

Regards,

Pat