Forum Discussion
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
| 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
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
3 Replies
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
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
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"
