Hi.
I have started working with DataFlows. The source database I'm currently using is on-prem. Across this database the keys/identifiers are two-column composites. To combine these columns in Power Query, I have to go to each entity and manually combine all sets of columns. You can probably imagine this is time-consuming; has been error-prone (having to specify the separator); and is generally not very sustainable going forward.
Does the community have any suggestions about handling this?
Can I programatically implement the merge, such as using a power query function? I've used a function to programatically create queries from an API source, by passing certain parameters, but I've not really seen how I could use a function to do this. Perhaps because the function isn't creating a source but modifying/transforming within the entity.
Is it something that may be done with DataFactory? I'm not sure how possible that would be as the database is on-prem. Otherwise I could build the logic in SSIS; but there's further considerations for me about using SSIS, such as when and how often to update/refresh, and the fact that we're trying to move away from SSIS.
Cheers.
