Split Column Into 2 Columns Based on Key Column

[jbenedict2]

Hi!

I am trying to manipulate the following table such that for every APP_NUM there is only one row.  Currently, the table is structured such that for every PROGRAM an APP_NUM is assigned, there is one row, resulting in the possibility of there being more than one row per APP_NUM. 

Please see the following for the current structure (I've marked the sample rows where there's a repeat of an APP_NUM because there's more than one PROGRAM):

I'd like the output to look like this (I've marked the APP_NUM that previously had 2 rows and is now combined into one row):

The result adds a column for Program2.

I've tried pivoting and grouping, but am not getting anywhere (probably due to my lack of proficiency in M). 

Any help is greatly appreciated!

[Greg_Deckler · Accepted solution]

@jbenedict2 Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjSxUNJRMjIwMjI3ADJKUitKDIF0pFKsDlDSyMgUXdYILmtsaoah1xivrAmyyWbosqYQ2VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [APP_NUM = _t, TERM = _t, PROGRAM = _t, ACTIVE_FLAG = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"APP_NUM", Int64.Type}, {"TERM", Int64.Type}, {"PROGRAM", type text}, {"ACTIVE_FLAG", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"APP_NUM"}, {{"TERM", each List.Max([TERM]), type nullable number}, {"ACTIVE_FLAG", each List.Max([ACTIVE_FLAG]), type nullable text}, {"PROGRAM", each _, type table [APP_NUM=nullable number, TERM=nullable number, PROGRAM=nullable text, ACTIVE_FLAG=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([PROGRAM],"PROGRAM"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"PROGRAM"})
in
    #"Removed Columns"

[jbenedict2]

@Greg_Deckler  Thank you for the super-fast response! 

I was able to merge the code that you provided with my existing code in the Advanced Editor, and it is now working.

I really appreciate the help!

Jess