Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors