Combine data from 2 columns in a single table

[DaveCor]

I have a table of data where I have a reference ID column and 2 other columns containing data. I need to combine the 2 data columns into 1 while retaining any ID's associated with each data point. 

I have looked at various options (COMBINE, MERGE, APPEND, PIVOT etc) and none seem to do what I need so I'm a bit stuck as to how to go about this. In all probability I am missing something very obvious as to how to do this. 

Data currently looks like this

ID	Sale Code	Transaction Code
123	Alpha
234		Beta
345	Gamma
333	Alpha
333	Beta
555	Beta	Gamma
456	Gamma
567		Beta

What I need it to look like is as follows

ID	Sale Transaction Code
123	Alpha
234	Beta
345	Gamma
333	Alpha
333	Beta
555	Beta
555	Gamma
456	Gamma
567	Beta

I would appreciate any tips anyone has on how to go about this

[DaveCor]

That looks like it should work and makes sense, but I am getting this

What am I missing?

let
Source = Excel.Workbook(File.Contents("C:\Users\admin\Desktop\Book1.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", Int64.Type}, {"Sale Code", type text}, {"Transaction Code", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Sale Code] & "," & [Transaction Code]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] <> " ")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Sale Code", "Transaction Code"})
in
#"Removed Columns"

[Thingsclump · Accepted solution]

Hi @DaveCor 

Below steps worked for me.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcswpyEgE0gpKsTrRSkbGJmC2jpJTakkiWMjYxBTIdU/MzUUoMzbG1AoRA2uDCZmamiKEICaAhE1MzTBMNDUzR7E4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Sale Code" = _t, #"Transaction Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Sale Code", type text}, {"Transaction Code", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Sale Code] & "," & [Transaction Code]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] <> " ")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Sale Code", "Transaction Code"})
in
#"Removed Columns"

Thanks,

Thingsclump team

https://www.thingsclump.com/ 

[DaveCor]

I had to add a step to account for my null values, but otherwise your solution worked like a charm. Thanks for taking the time to look at this, appreciate it