Forum Discussion

Belle2024's avatar
Belle2024
Helper 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
Helper I
2 years ago

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]

 

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

    • dufoq3's avatar
      dufoq3
      Community Champion
      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"