Forum Discussion
Combining results from different columns
Change address to document
let Source = Excel.Workbook(File.Contents("C:\Downloads\sample data (1).xlsx"), true, true), #"Completed 2024!_xlnm _FilterDatabase_DefinedName" = Source{[Item="Completed 2024!_xlnm._FilterDatabase",Kind="DefinedName"]}[Data], #"Changed Type" = Table.TransformColumnTypes(#"Completed 2024!_xlnm _FilterDatabase_DefinedName",{{"sort code", Int64.Type}, {"Account number", Int64.Type}, {"If not 12345", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [If not 12345] <> null then Text.From([If not 12345]) else Text.Combine({Text.From([sort code]), Text.From([Account number])}, " "), type text) in #"Added Custom"
Hi Belle2024, check this.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBISMgUIrViVYysTC3gAhBuGamRkC2oaWlCUwISJuYWZjCNcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]),
ReplaceWithNull = Table.TransformColumns(Source, {}, each if List.Contains({null, "null", ""}, Text.Trim(_)) then null else _),
Ad_Combined = Table.AddColumn(ReplaceWithNull, "Combined", each
[ a = Text.Combine({[Data 1], [Data 2]}, " "),
b = if a <> "" then a else [Data 3]
][b], type text)
in
Ad_Combined
I can't get this to work for me, I may not be advanced enough for this! 😂
I was hoping to be able to do a custom column at the end of my existing data that will update each time I refresh my data which is updated daily.
You should read note at the bottom of my post if you don't know how to use my query.
But you can also add this as custom column. (Edit column names [Data 1], [Data 2] and [Data 3] if necessary)
[ a = Text.Combine({[Data 1], [Data 2]}, " "), b = if a <> "" then a else [Data 3] ][b]Hi, thanks for the response. I did read the notes at the bottom and changed the source and column names to match my data but I was only getting the data in your table and it still gave an error when it should of been displaying the number in data3 🤷
I tried the below changing the column names to match the column names in my actual data but the new column just displayed error in each row. Would this be because it says null rather that blanks in the columns the data is coming from?
[ a = Text.Combine({[Data 1], [Data 2]}, " "), b = if a <> "" then a else [Data 3] ][b]Could you provide few rows of your real data? Replace sensitive data if necessary. Upload it to google drive and paste here a link (don't forget to set read access for everyone).