cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaveCor
Helper I
Helper I

Combine data from 2 columns in a single table

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

 

IDSale CodeTransaction Code
123Alpha 
234 Beta
345Gamma 
333Alpha 
333Beta 
555BetaGamma
456Gamma 
567 Beta

 

What I need it to look like is as follows

 

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

 

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

1 ACCEPTED SOLUTION
Thingsclump
Resolver V
Resolver V

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"

 

Thingsclump_0-1638987054335.png

 

Thanks,

Thingsclump team

https://www.thingsclump.com/ 

 

View solution in original post

3 REPLIES 3
DaveCor
Helper I
Helper I

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

 

DaveCor_0-1638988017664.png

 

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
Resolver V
Resolver V

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"

 

Thingsclump_0-1638987054335.png

 

Thanks,

Thingsclump team

https://www.thingsclump.com/ 

 

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors