Import multiple columns in SP list to same column in CDS

[JRuss]

Is there any way to collate multiple SharePoint columns into only two columns in CDS?  So rather than a direct 1 to 1 import, create sort of a stacked table configuration.  Please see screenshots... Note that the second screenshot is Excel, but representative of an SP list...

[jlindstrom · Accepted solution]

So here is what I have done:

add a column in excel and concatenation the multiple columns into a new column

do your integration from that field

also depending on your dat migration tool, you can build a formula to concatenate multiple fields.

[JRuss · Accepted solution]

That makes sense.  I was hoping for something a little more OOB since I have about 20 lists I need to bring into these same two columns, but this is a clever workaround (Export to Excel, stack them all).  I don't have a data migration tool, so I think your recommendation is going to be my best bet.  Thanks for taking the time to respond on a Friday!

[jlindstrom · Accepted solution]

There are over a dozen ways to import data from excel into cds. Power Automate is one option that is out of the box, and it can easily concatenate multiple column values into a single cds column 

[JRuss]

Ooh, I like that method even better.  Thanks again!