cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthjukuri
Frequent Visitor

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

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

 

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@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
Frequent Visitor

@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.

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
Super User IV

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors