Forum Discussion

Rookarumba's avatar
Rookarumba
Helper III
9 years ago
Solved

Excel 2016 : Get and Transform - Add column that include blanks

Hi Guys/Ladies,

I'm new to Excel Get & Transform but not new to Excel.

Anyway, I needed to create a Unique Id with the available information on my sheet and I used " & " to join all the text.
Since I'm having about 500k row of data thus that get a bit sluggish which prompt me to use Get & Transform.

Here the issue, with Get & Transform I used "& " to join various column together but  If any of my cells is blank, it returns a null on the new column but I still need the info even if is blank (like in Column F)


Is there any ways to resolve this in Get & Transform?

Thanks,

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Maybe I'm overcomplicating the issue... :smileyfrustrated:

     

    Proably the easiest solution is to replace null values with blanks (select the column, choose "Replace Values" on the Transform" tab, enter "null" in "Value To Find" and leave "Replace With" empty).

    Now you can just concatenate the values with & (using Text.From for non-text columns).

     

    Just in case someone is interested: this video illustrates how to implement the function solution from my previous post.

9 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    If you want to combine text values from the first 5 columns, you can add a column with the following formula:

     

    = Text.Combine(List.FirstN(Record.FieldValues(_),5))

     

    The same, a little bit more complicated if you have non text values: 

     

    = Text.Combine(List.Transform(List.FirstN(Record.FieldValues(_),5),each Text.From(_)))

     

    • Rookarumba's avatar
      Rookarumba
      Helper III
      9 years ago

      Thanks Marcel,

       

      My original data are not side-by-side as in the pic (sample data), there are other columns in-between.

      I assume your formula is picking from 1st to last column right? How do I write a formula for this type of situation.

       

      Many Thanks

       

       

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        That's why it is so important to provide respresentative example data.

         

        Anyhow, that complicates matters; I've written this custom function, that will give you the combined values of the Columns, which is a list with either column numbers (base 0) or column names or mixed names and numbers).

        It returns the values in the sequence of the provided Columns; you may even include 1 column multiple times.

         

        let
    CombineColumnValues = (Source as record, Columns as list) =>
let
    ColumnPositions = List.Transform(Columns, each if _ is number then _ else List.PositionOf(Record.FieldNames(Source),_)),
    ColumnValues = List.Transform(ColumnPositions, each Record.FieldValues(Source){_}),
    CombinedColumnValues = Text.Combine(List.Transform(ColumnValues,each Text.From(_)))
in
    CombinedColumnValues
in
    CombineColumnValues

         

        As an example, the following code will give you the combined values from the 5th and "SomeNumber" and the 3rd columns of table Data:

         

        let
    Source = Data,
    AddedKey = Table.AddColumn(Source, "Key", each CombineColumnValues(_,{4,"SomeNumber",2}))
in
    AddedKey

         

        Note: the last line is created via option "Add Custom Column" on the "Add Column" tab; it is not possible to use option "Invoke Custom Function", because you won't be able to supply the required parameters.
        Should you want to adjust this line of code, then it can only be done via the formula bar or the advanced editor. If you would use the gear button behind the name of the code step, then the "Invoke Custom Function"  popup pops up and you won't be able to use this.

         

        By the way: this is rather advanced Get & Transform (a.k.a. Power Query a.k.a M) which can not be expected to be created by someone who is new to this.