-1

I have 2 columns X and Y having comma separated values. I want to create a custom Column XY that will have data from both the strings in columns X and Y.

The below screenshot is the sample format in spreadsheet and need this to be achieved in the Power BI Report.

Sample Columns

I am new to Power BI. Do help me find a solution.

Thanks in advance

hongsy
  • 1,498
  • 1
  • 27
  • 39
Harish Srikanthraj
  • 11
  • 1
  • 1
    It is unclear what you are asking. The two strings are not concatenated and there seems to be an algorithm to how the column values are merged together, please add further details, and also what you have tried already to address this. – Murray Foxcroft Jan 28 '20 at 11:45

1 Answers1

0

If you are simply looking to concatenate the fields, You can do that using the & operator:

Concat_Field = Table[column X] & "," & Table[Column Y]

If you want to pick alternative values from X and Y and if the there are always 4 values, you can use:

Column XY = 
        LEFT('Table'[Column X],2) & LEFT('Table'[Column Y],2) & 
        MID('Table'[Column X],3,2) & MID('Table'[Column Y],3,2) & 
        MID('Table'[Column X],5,2) & MID('Table'[Column Y],5,2) & 
        MID('Table'[Column X],7,2) & "," & MID('Table'[Column Y],7,2)

If the above two cases are not what you are looking for, then kindly provide additional details, so that we can help you out.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
  • @ CR7SMS Thank you for the quick response. But the number of values in the X & Y columns will not be constant and can differ for each row. Is there a generic way to manage this? – Harish Srikanthraj Jan 30 '20 at 08:31