cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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

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

 

1)  Have data such as:  

 

Johna|b|d
Jimb|e
Janea|b|e
Josef
Jilld|f
Joannea|b|e|f

 

2)  Use delimiter to yield:  

 

 abcdef
Johnabd   
Jimbe    
Janeabe   
Josef     
Jilldf    
Joanneabef  

 

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:   

 abdef 
Johnabd   
Jim b e  
Janeab e  
Jose    f 
Jill  d f 
Joanneab ef 

 

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

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors