Spliting (inconsistent data) column into multiple columns - Power BI

[srikanthjukuri]

Hi,

I have a table with a column of data having multiple pieces of information in an irregular pattern.  please help me to split data into multiple columns as many as possible - campaign name, country, year, quarter, product, etc. 

Source column (Campaign)

17Q3-Campaign AUK-SOC-SB-ACQ-Social

Campaign A-FI-16Q1-ATL-SB

Campaign A-FI-19Q4-ATL-GOC-WelcomeOffer-Dec

Campaign A-FI-17Q2-Q4-ATL-SB-Christmas

Campaign A-FI-16Q4-BTL-GOC-CA

Campaign A-NO-16Q4-BTL-GEN-Xmas
Campaign A-NO-16Q4-BTL-GOC-WB
Campaign A-NO-16Q4-BTL-SB-WB
Campaign A-PL-16Q4-ATL-SB-Goals
Campaign A-PL-16Q4-ATL-SB-Welcome offer
Campaign A-PL-16Q4-BTL-SB-DF
Campaign A-SE-16Q4-ATL-SB-Context
Campaign A-SE-16Q4-ATL-SB-NBA-London
Campaign A-SE-16Q4-ATL-SB-Native
Campaign A-SE-16Q4-ATL-SB-PL-December
Campaign A-SE-16Q4-ATL-SB-PL-Vias
Campaign A-SE-16Q4-BTL-GOC-Dynamic
Campaign A-SE-16Q4-BTL-NBA-London
Campaign A-SE-16Q4-BTL-SB-Dynamic
Campaign A-UK-16Q4-ATL-SP-ESPN
Campaign A-UK-16Q4-ATL-SP-TalkSport
Campaign A-UK-16Q4-BTL-GOC
Campaign A-UK-16Q4-BTL-SB
DART Search
Campaign A-WW-16Q4-ATL-SB-Sponsorship
Campaign B-BR-16Q4-BTL-SB-Genius
Campaign B-DE-16Q4-ATL-SB-Ankama
Campaign B-FI-16Q4-ATL-GEN-AIP
Campaign B-FI-16Q4-BTL-GOC-WB
Campaign B-FI-16Q4-BTL-GOC-WB-Projects
Campaign B-FI-16Q4-BTL-SB-DF
Campaign B-NO-16Q4-ATL-VID-Christmas

Column information break down - Campaign name using abbreviations to indicate CampaignName(ex. campaign A), country, year/ quarter, product (SB = Specialbooking, GOC = gatewayofcontact)

Thanks,

PBI

[V-pazhen-msft]

@srikanthjukuri 

Is it possible to change the data structure of the source table? I am thinking the easiest way is if you can just replace the first row to the same format like the rest. Then you can simply split with "-" in Power Query.

17Q3-Campaign AUK-SOC-SB-ACQ-Social (Campaign AUK-SOC-17Q3-ACQ-SB-Social)

Campaign A-FI-16Q1-ATL-SB

Campaign A-FI-19Q4-ATL-GOC-WelcomeOffer-Dec

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[srikanthjukuri]

@Greg_Deckler  Thanks for the quick response. What I was wondering if there is a way to code with conditions at row level.

1. Campaign name: Search each row for word 'Campaign' and populate till the left most delimiter with custom '-', else populate what ever the row has. As no other information like year/quarter, product no other information is available if campaign word is not present. 

2. Country: It is always followed by campaign name, else null

3. Year/quarter: typical format '17Q1', 'Q2', 'Q3-Q4', '16Q2-Q3' - Can we use reference of other column 'date' here directly? 

4. Market type: only three types 'ATL', 'BTL' or 'TTL' else null

5. Product type: only two types SB or GOC else null

6. Promotion type: 1st condition all the infomation followed by SB or GOC, If else market type (ATL, BTL, TTL) else null

I hope this will give a better understanding of the data for all the users.

[Greg_Deckler]

@srikanthjukuri You can using calculated columns in Power Query editor or DAX but your process won't work because your first line is missing the delimiter between campaign and country so it would mess up.

[Greg_Deckler]

@srikanthjukuri I don't see a workable solution for this because of your first line. Maybe @ImkeF has an idea. If not for that line, would be trivial. I would work to ensure your data comes in more consistently because that first line of yours is going to destroy your attempts to parse this correctly.