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

[Rookarumba]

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,

[MarcelBeug]

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]

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]

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.

[Rookarumba]

Thanks Marcel,

Sorry for the trouble, I'm going to give it a try.

I didn't know is this complicated as I wanted to combine those columns but didn't expect to get a null values if any of cell is blank.

Thought it would work just like the "&" function in Excel.

If I only want to join the Column in Red and skip those non-red columns, do I still apply the same code?

Alternatively, I can just delete those unnecessary columns and apply the first code you wrote.

Thanks

[MarcelBeug · Accepted solution]

Maybe I'm overcomplicating the issue...

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.

[Rookarumba]

Thanks Marcel,

That works too 🙂 & Thanks for creating the video, give me a better understanding of how the syntax works.

=CombineColumnValues(_,{0,2,"Product desc","Date Purc"})

Few questions here,

What does (_, mean/represent?

I can replace "Product desc" and "Date Purc" with their respective column no. right ?

[MarcelBeug]

In general the _ is used in combination with keyword each.

It represents the current value.

In Table functions, it is the current record.

In List function, it is the current element.

As an example if you want to trim the elements of a list with texts, then you can use the following 3 equivalent alternatives:

= List.Transform(<list>, Text.Trim)

= List.Transform(<list>, each Text.Trim(_))

= List.Transform(<list>, (x) => Text.Trim(x))

The first alternative can only be used if the functiom does not require any further arguments.

The second alternative is required to supply additional arguments, e.g. if you want to remove leading/trailing other characters then <space>. Or in our case to supplu the list of columns to be removed.

Yes, you can replace columns names with their respective column numbers.

Within the function, any column names are converted to their position (as you can see in the code I provided).

[Rookarumba]

@MarcelBeug, i'm hoping to pick your brain regarding using PowerPivot / Power BI instead of using Access to create a database.

I see that PowerPivot/BI are not limiting by rows anymore and i'm wondering if it is better than Access. I tried to google on this topic but i can't find anything on it.

The scenario here is, I'm leaning more toward PowerPivot/BI since it give me more flexibility to manipulate the data and do analysis on it but I need other users to collaborate on certain sheets/file, like what Access have. (I think there a 'collaborate' function in Excel but I haven experiment it in Excel yet)

1. Since we're not working on a historical data, instead we are downloading batches of data every quarter and churn out reports from it. (I can use PowerPivot to create relationship and churn out the same report)
2. We are working with Million of rows (Excel can handle that already)
3. We need collaboration with a handful of user and somehow update us when someone makes a new input in the file.

Do you think PowerPivot/BI can handle these or Access ?

Thanks

[Rookarumba]

Sorry for the late reply. Was swarm with work and thanks for the explanation, i just pick up a book on PowerQuery & PowerPivot.
Btw, i found an easier way, which is to "Add Column" -> "Combine" the respective column, this way i don't have to deal with the 'null'