SQL to split a column by delimiter

[mark_carlisle]

I'm building a report using direct query rather than import as I would like users to see real time data. This is the first time using this and a few of the functions I would usually use in the query editior to model the data don't work for direct query. The main issue being that I am unable to split a column by a delimiter. I've done some brief research and it would appear that I'm going to use SQL to split the column before it gets to Power BI. The question is how do I do this?

My query so far is;

SELECT [IncidentId]
      ,[IncidentReference]
      ,[AccountNumber]
      ,[CreateDate]
      ,[CreateStaffDetailId]
      ,[ModifyDate]
      ,[CurrentStaffDetailId]
      ,[CurrentIncidentStatusId]
      ,[CurrentProductDescription]
      ,[CurrentClassificationText]
  FROM [***].[dbo].[***]

The column I need to split is CurrentClassificationText, Currently it is stored in the database and then pulled into Power BI like this;

CurrentClassificationText

[ClassificationTier1] > [ClassificationTier2] > [ClassificationTier3] > [ClassificationTier4]

I need to have the data in Power BI like this;

CurrentClassificationText1	CurrentClassificationText2	CurrentClassificationText3	CurrentClassificationText4
[ClassificationTier1]	[ClassificationTier2]	[ClassificationTier3]	[ClassificationTier4]

[Anonymous]

@mark_carlisle,

You can try this solution out using DAX to see if it works

[mark_carlisle · Accepted solution]

Thank you, I tried this earlier and was getting no where with it. I've checked Allow unrestricted measures in DirectQuery mode and this is now working.