Help with Data Transformation: Merging Multiple Columns

[cborrero]

"Hello,

I'm in the process of merging multiple data columns into a single column. My aim is to combine the 'Objectives FK Program Increment ID' with the 'PI Details Program Increment ID'. As depicted in the provided sample dataset, the Objectives are currently not linked to any other fields.

Additionally, I need to merge the following columns into the 'PI Details FK Program Increment ID': 'Epic FK Program Increment ID', 'Feature FK Program Increment ID', and 'Story FK Program Increment ID'. Initially, I thought it was necessary to merge only the 'Objective FK Program Increment ID' and 'PI Details FK Program Increment ID'. However, upon further inspection, I realized that rows lacking an 'Epic Forecast ID' also lack data in the 'Epic FK Program Increment ID' column (thus, rows without an 'Epic Forecast ID' might be excluded if null).

My main challenge lies in merging these columns while managing the expected inherent duplicates in our data. Unfortunately, due to work restrictions, I can't share my workflow or the actual data. Nonetheless, I seek general advice on the most effective approach to accomplish this merge. I have considered various methods, including the transpose tool, but I'm uncertain which would be the most effective. I would greatly appreciate any suggestions or guidance."

This is the current data set without the merge of columnsThis would be an expected outcome

[binuacs]

@cborrero What is the expected output? can you upload the expected output result?

[cborrero]

Hi @binuacs

I made a slight change to the data set and exclude some columns that were not needed. I updated post which includes an expected outcome.

[danilang]

Hi @cborrero 

It looks like you want to fill the value of Details Inc id with any of the other Inc ID fields.  You should be able to do this 

if IsEmpty([Details Inc ID]) then
	max([Epic Inc ID],[Feature Inc ID], [Story Inc ID])
else
	[Field1]
endif

Dan