How can I add a column merged by dynamic columns

[Tooley]

Hi,

I'm having a little trouble with the correct syntax to add a new column (not replace existing) which is merged from a dynamic list of column names.

The attached shows the expected result using the built in function 'Add Column > Merge Columns' (see query "tData_AddMergedColumn", which I'd like to replicate but make dynamic by merging from a list of column names. If I just pass the list through then the column names are merged instead of the field values (see "tData_AddMergeDynamicColumn"). I guess I need another 'each' or Record.FieldValue reference, but I keep getting errors.

Can anyone please help?


Thanks

 

[Bill Szysz]

Hi Tooley,
There are many ways to do this. Below, two of them.

#"Added Custom" = Table.AddColumn(Source, "Merged", each Text.Combine(Record.ToList(Record.SelectFields(_, lstConcatFields)), " "))

 #"Added Custom" = Table.AddColumn(Source, "Merged", (x) => Text.Combine(List.Transform(lstConcatFields, each Record.Field(x, _)), " "))

 

[Tooley]

Fantastic, thanks Bill

 

[Tooley]

Hi Bill,

Using the following, how can I amend so it merges all fields including nulls? :

#"Added Custom" = Table.AddColumn(Source, "Merged", (x) => Text.Combine(List.Transform(lstConcatFields, each Record.Field(x, _)), "_"), type text)

Thanks

 

[Tooley]

I've managed to figure our a workaround by replacing null values with "blank":

#"Added Custom" = Table.AddColumn(Source, "Merged", (x) => Text.Combine(List.ReplaceValue(List.Transform(lstConcatFields, each Record.Field(x, _)), null, "blank", Replacer.ReplaceValue), "_"), type text)