Forum Discussion

Belle2024's avatar
Belle2024
Icon for Helper I rankHelper I
2 years ago
Solved

Combining results from different columns

  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 bot...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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"

     

Belle2024's avatar
Belle2024
Icon for Helper I rankHelper I
2 years ago

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.

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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]