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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Belle2024
Frequent Visitor

Combining results from different columns

image002.png

Hi I am looking for some help, I am trying to create a new column that will combine the data in columns Data 1 and Data 2 and if one is blank it will return the value of the other and if both cells are blank it will return the value in Data 3. First I tried to combine the columns Data 1 and Data 2 and then do another column to look up to data 3 if the combined columns had a blank value but the combined column only returned a value when there was a value in both the data 1 and 2. I also tried to create a custom column using if [Data 1] <> null && [Data B] <> null then [Data 1] & [Data 2] else if [Data 1] <> null then [Data 1] else [Data2] but again I haven’t got it quite right and it only displays the values if there is something in both columns. I am wondering if there is a way to do this in just one column? I have included a table that shows what I want it to return from the three columns

 

1 ACCEPTED SOLUTION

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

7 REPLIES 7
dufoq3
Super User
Super User

Hi @Belle2024, check this.

 

Result

dufoq3_1-1713368225784.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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]

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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).


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors