How To Import Comma Delimited Values Within a Single Column

[WinterMist]

Hello Community - Could someone advise me how to import comma delimited values within a single column?

In the past, this just worked without taking any specific steps.

But now, it's behaving differently.

Excel column SR_RejectionReasonID contains comma delimited values.

When importing from Excel into Power BI, the preview looks as follows.

In Power Query, the default data type of SR_RejectionReasonID = Decimal Number.

Changing the data type to Text brings all the numbers back, but without the comma delimiter.

Can someone advise how to get the data in Power Query to appear exactly as it does in Excel (numbers with delimiter)?

Regards,

Nathan

[SamWiseOwl · Accepted solution]

Hi @WinterMist 

When PBI imports the data and after some steps it auto adds a Change Type step.

You could delete this step to prevent it assigning the numeric data type.

Or click on the step when you change the datatype and see this message:

Choose Replace current to ensure that this column was ALWAYS text.

Not converted to number and back to text.

Alternitvely modify the M of the changed type step.

Change this from type Number to type text.

My guess is that your data has changed. The data type is based on the first 1000 rows, in the past maybe the first 1000 only had numbers.