Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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"
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