Colum need to separate by the data category

[Anonymous]

Hello Everyone, 

Please can someone help me to achive the below output uisng PowerBI 

Original Data 

Sector Path

AEG_BAML_CASH.N/A BAML RATING. BAE3N75F8

AEG_BAML_CASH.N/A BAML RATING. BAE3N75F7

AEG_BAML_CASH.N/A BAML RATING. BAE3N75G8

AEG_BAML_CASH.N/A BAML RATING. BAE3N75F9

AEG_BAML_CASH.N/A BAML RATING. BAE3N75H8

BY_RCOUNTRY_AEG.North America.United States.BAE83QD3

BY_RCOUNTRY_AEG.Europe.Germany.BAE83QD4

BY_RCOUNTRY_AEG.BAE83QD5

BY_RCOUNTRY_AEG.United States.BAE83QD6

BY_RCOUNTRY_AEG.United States.BAE83QD7

Data After Split by delimeter 

Sector Path1	Sector Path2	Sector Path 3	Sector Path4
AEG_BAML_CASH	N/A BAML RATING	BAE3N75F8
AEG_BAML_CASH	N/A BAML RATING	BAE3N75F7
AEG_BAML_CASH	N/A BAML RATING	BAE3N75G8
AEG_BAML_CASH	N/A BAML RATING	BAE3N75F9
AEG_BAML_CASH	N/A BAML RATING	BAE3N75H8
BY_RCOUNTRY_AEG	North America	United States	BAE83QD3
BY_RCOUNTRY_AEG	Europe	Germany	BAE83QD4
BY_RCOUNTRY_AEG	BAE83QD5
BY_RCOUNTRY_AEG	United States	BAE83QD6
BY_RCOUNTRY_AEG	United States	BAE83QD7

Expected Output 

Sector Path1	Sector Path2	Sector Path 3	Sector Path4
BY_RCOUNTRY_AEG	North America	United States	BAE83QD3
BY_RCOUNTRY_AEG	Europe	Germany	BAE83QD4
BY_RCOUNTRY_AEG			BAE83QD5
BY_RCOUNTRY_AEG		United States	BAE83QD6
BY_RCOUNTRY_AEG		United States	BAE83QD7

 @dax @Desktop 

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Sector Path2_column =
var _left=LEFT([Sector Path2],3)
return
IF(_left in {"Uni","BAE"},BLANK(),[Sector Path2])

Sector Path3_column =
var _left=LEFT([Sector Path3],3)
return
IF(_left in {"Uni","Ger"},'Split_Table'[Sector Path3],
IF(
    _left in {"BAE"},"United States",BLANK()
))

Sector Path4_column =
var _left=LEFT([Sector Path4],3)
var _1=CALCULATE(MAX('Split_Table'[Sector Path2]),FILTER(ALL('Split_Table'),'Split_Table'[Sector Path1]=EARLIER('Split_Table'[Sector Path1])&&'Split_Table'[Sector Path2]=EARLIER('Split_Table'[Sector Path2])))
var _2=CALCULATE(MAX('Split_Table'[Sector Path3]),FILTER(ALL('Split_Table'),'Split_Table'[Sector Path1]=EARLIER('Split_Table'[Sector Path1])&&'Split_Table'[Sector Path2]=EARLIER('Split_Table'[Sector Path2])&&'Split_Table'[Sector Path3]=EARLIER('Split_Table'[Sector Path3])))
return
IF(_left in {"BAE"},'Split_Table'[Sector Path4],
IF(
    LEFT(_1,3)in{"BAE"},_1,
    IF(
        LEFT(_2,3)in{"BAE"} ,_2,BLANK())))

2. Create calculated table.

Summarize_Table =
var _sum=FILTER('Split_Table',LEFT('Split_Table'[Sector Path1],2) in {"BY"})
return
SUMMARIZE(_sum,[Sector Path1],[Sector Path2_column],[Sector Path3_column],[Sector Path4_column])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Watsky]

Hey @Anonymous, 

Can you show us what the end result should look like?

[Anonymous]

Hello @Watsky updated