Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
rpiboy_1
Resolver I
Resolver I

Dynamically Merge Multiple Columns

Hi all, I want to dynamically merge columns in a function.

 

Let's say I have this fairly 'standard' PQ expression:

 

 

#"Inserted Merged Column" = Table.AddColumn(#"PreviousStepp", "NewColumnName", each Text.Combine({[Column1], [Column2]}, "."), type text)

 

 

The result would be:

 

Column1Column2NewColumnName
ABA.B
CDC.D

 

Now, I'd like to make this expression dynamic in a function where I pass the columns to be merged. So the code might look like this:

 

(ColumnNamesList as list) =>

#"Inserted Merged Column" = Table.AddColumn(#"PreviousStepp", "NewColumnName", each Text.Combine(ColumnNamesList, "."), type text)

 

 

The problem is that Text.Combin expects an explict column name reference bracketed i.e. '[Column1]'.

 

Whereas the list will pass a list of Text values that are not converted to expclit column references (even if brackets are included)

 

i.e. this list will not work

List
[Column1]
[Column2]

 

It will be passed as {"[Column1]", "[Column2]"} and Text.Combine will fail.

 

How do I dymically pass a list of column name references that will function with the Text.Combine and respect the 'each' iterator for the rows? Normally I would use:

 

 

Record.Field(_, varColumnName)

 

 

But I don't know how to dynamically expand that expression to iterate Record.Field over each Column in the list?

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

Here is one way to do it. You can provide the colnames as a concatenated text string and parse it in the function.

 

//fnAddMergeColumn
let
    Source = (inputtable as table, newcolumn as text, colnames as text) => 
        let 
        
            result = Table.AddColumn(inputtable, "Concatenated", each Text.Combine(Record.ToList(Record.SelectFields(_, Text.Split(colnames, "-")) ), "."))
        in 
            result
in
    Source

ppm1_0-1669773068983.png

 

Microsoft Employee

View solution in original post

5 REPLIES 5
PhilipTreacy
Super User
Super User

Hi @rpiboy_1 

 

I don't see the advantage of creating another function to do this.  At some point you have to declare what ColumnNamesList is so not sure how 'dynamic' your desired solution is.

 

Why not just concatenate the columns ?

 

= [Column1] & "." & [Column2]

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


FYI, to close the loop on this. I did not end up writing this as a seperate function, but it was helpful to see the solution written as a stand-alone function from the perspective of parsing the code to understand how to adopt it to my own needs.

 

I was actually able to drop the 'Text.Split' as in my real function I already have the columns names as list, so conviently I'm able to just pass that list directly and it all works!

 

Thanks again!

ppm1
Solution Sage
Solution Sage

Here is one way to do it. You can provide the colnames as a concatenated text string and parse it in the function.

 

//fnAddMergeColumn
let
    Source = (inputtable as table, newcolumn as text, colnames as text) => 
        let 
        
            result = Table.AddColumn(inputtable, "Concatenated", each Text.Combine(Record.ToList(Record.SelectFields(_, Text.Split(colnames, "-")) ), "."))
        in 
            result
in
    Source

ppm1_0-1669773068983.png

 

Microsoft Employee

@ppm1 brilliant! I should be able to adopt this. Fresh eyes in the morning helps. I was missing the combination of Record.ToList, SelectFields with a Text.Split.

 

Thanks much!

ok, this has me thinking... and I think this is kinda of what your code is doing.

 

I could use my Text list of of column names to isolate the columns I'm interested in merging into a seperate table, as a Table I should be able to just merge all the associate columns, then pass the merged values as a column back into my original table through a merge. Will tackle this further in the morning.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.