How to concatenate regardless of data type?

[Keith011]

Hi there.

How can I concatenate multiple columns (at least 10 columns) together regardless of the data type.

The reason why I state regardless of data type is because of all the 10 columns i have, the data is very dynamic it can be all numeric like "1234567", it can be alphanumeric "abc123abc123", it can be "123-abc-def", it can be "123/kfj/2021", it can be "-", it can be a null value.

What i've tried is transform all these column that I want to concatenate into dataType = Text and do the traditional merge, it gives me some error for some records (not all records). And I've also tried the Column1&" "&Column2&" "&Column3 method, it also gave me some error for some rows. 

error looks like this (but note that i have already change the type to Text for all columns before i do the concatenating)
DataFormat.Error: We couldn't convert to Number.
Details: 

What's the best approach to generalize all the columns and just concatenate them no matter the value or data type, just concatenate it like how excel would be able to do so with =CONCATENATE function

Thanks in advance

[Keith011]

I managed to solve this. But it's not a "Solution".

What i did was copy and paste the exact data to a new column and concatenate the new column and it worked.

So it's still a mystery of why the original column doesn't work. 

I got this idea from the link post above that i shared

[Keith011 · Accepted solution]

I found this post which is similar to my case but no solution found @Vijay_A_Verma 
https://community.powerbi.com/t5/Desktop/Error-quot-Can-t-convert-to-number-quot-but-the-collumn-typ...

[Vijay_A_Verma]

Use below type of construct

= Text.Combine(List.Transform({[Column1],[Column3],[Column5],[Column6]},each Text.From(_)),", ")

if records are consecutive, then we need not specify Column1 etc in the list. We can take values in Record.ToList and parse the list from there.

[Keith011]

@Vijay_A_Verma 

The error still persist for some records. Some errors below :

DataFormat.Error: We couldn't convert to Number.
Details:
8382/A/2018

DataFormat.Error: We couldn't convert to Number.
Details:
--

DataFormat.Error: We couldn't convert to Number.
Details:
Oo43315

DataFormat.Error: We couldn't convert to Number.
Details:
10972/2019

This is the part where im confused, why are those errors that appearing? those could be "easily" be read by Text right?

I had my current method in 1 column , and another column on your solution , side by side comparison, the errors are appearing on both my column and your column as well for the same record 

[Vijay_A_Verma]

Can you apply a filter for Error record, load in an Excel and share through Onedrive few records to investigate? My initial guess is that these contain some invisible characters which we need to investigate and tackle them. 

You can remove sensitive data but keep the data which is giving error? 4-5 records would be enought to handle this.

[Keith011]

@Vijay_A_Verma 

I'm not sure how to actually export the errors but I did a small investigation : 

There are say 10 columns that I want to concatenate. I did it 1 by 1 to see which is the actual column that caused the error. So what I did is (with the solution you provide), I put column1 and column 2 then run it. No error. then i do column1, column2 and column 3. No error. then Column 1,2,3 and 4. No error. Until I did Column 9 and 10, these are 2 culprits that is causing the error.

below here is the excel link that i created. It contains the "Column 9" and "Column 10" unique values. Those that are highlighted in yellow are the ones that are causing the error (just highlighted a few) but do note that those values that are causing the error, the similar format also appears in other columns which worked fine

Book3.xlsx