Changing data format in column with 2 types of formatting

[Vectorize]

I would like to change a column format to show the same format for all nationalities. My source data however, has 2 different options in the column, e.g.:  UNITED KINGDOM (GB) and GB/ United Kingdom. This is how the data appears for a number of different nationalities. 

I have tried to extract before delimiter ( and after / but the second extract removes all other data unless it contains a / 

Realise Dax conditions could be an option but there are nearly 80 variations to include. Is there an easy way to do this?

Here is a small sample of the data set and the expected outcomes Nationalities.xlsx

I'm very new to PowerBI - any help would be appreciated.

Thank you 

[Ashish_Mathur]

Hi,

The 2 blue tables are the input tables named as Data and Country.  This M code creates the green table

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Custom1 = Table.TransformColumns(Source,{{"Nationality Original", each List.Accumulate(Country[Country],_,(seed,current)=>Text.Trim(Text.Replace(seed,current,"")))}})
in
    Custom1

Hope this helps.

[Vectorize]

Thank you for your response. I should have mentioned that my data is coming in via an Odata feed, so I cannot amend it at source before it does into Power BI. 

[Anonymous]

Hi @Vectorize ,

@audreygerred Thanks for your concern about this case!
If you can use Power Query, you can achieve this.
Put all of this M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3NCoMwEIRfZfFkoeAzrBplaYwlmpN4SDUtARuLtqfSd2/8KfSyzH4zO9s0AShBNUvhRCJPywLCPD4E7bEJkMcoCCFEvgNV1RL5itSGMhlBNmnXGXhb1w2v3syQjNNsO/3ZEygS9u+WsqIEPxBmcivJ4wiUs0/Tw8m6Wz/eV0wsAprMoF2/7SJdXpPYrkgutnbQLPMINA/6bjuQ5vG6DF6M13YPovAhP32o4lhQApKdVcy9KLPWV8pfJeMoUg+YB+0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Nationality Original" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nationality Original", type text}}),
    AddedCustom = Table.AddColumn(#"Changed Type", "Expected", each 
        let
            sourceText = [#"Nationality Original"],
            textAfterSlash = if Text.Contains(sourceText, "/") then Text.End(sourceText, Text.Length(sourceText) - Text.PositionOf(sourceText, "/") - 2 ) else sourceText
        in
            textAfterSlash),
    #"Split Column by Delimiter" = Table.SplitColumn(AddedCustom, "Expected", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Expected.1", "Expected.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Expected.1", type text}, {"Expected.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Expected.2"})
in
    #"Removed Columns"

And the final output is as below:

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

[Vectorize]

Thank you for your reply. I should have mentioned that my data is coming in via an Odata feed rather than an Excel file. I already have some M code which I have copied below. Do you know how I can reference the Source because if I copy the Source code from the current M code it doesn't work (I've blocked out any identifying information in the source code)? Grateful for any help! 

let
Source = Csv.Document(Web.Contents("https://query.XXXXX.com/data/TARGETconnect/students?key=XXXXXXXXXX.XXXXXXXXXXXXXX.net&_fmt=csv"),[Delimiter=",", Columns=50, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User_Id", Int64.Type}, {"Student_Number", Int64.Type}, {"Student_Active", Int64.Type}, {"Student_Details", Int64.Type}, {"Fee_Status", type text}, {"Nationality", type text}, {"Ethnicity", type text}, {"Disability", type text}, {"YearOfStudy", Int64.Type}, {"Type", type text}, {"Faculty", type text}, {"Department", type text}, {"Department_Code", type text}, {"Course", type text}, {"Course_Code", type text}, {"Course_Start", type datetime}, {"Course_End", type datetime}, {"Last_Modified", type datetime}, {"Daily_Job_Alerts", Int64.Type}, {"Weekly_Job_Alerts", Int64.Type}, {"Daily_Event_Alerts", type logical}, {"Weekly_Event_Alerts", type logical}, {"Bulk_Emails", Int64.Type}, {"Created_Date", type datetime}, {"Last_Login_Date", type datetime}, {"Unknown_Interests", Int64.Type}, {"Blocked", type text}, {"Blocked_Date", type text}, {"Blocked_Reason", type text}, {"Student_Location", type text}, {"JSON_Profile_Complete", type text}, {"Gender", type text}, {"Student_Tag", type text}, {"Email_Pref", type text}, {"Extended1", type text}, {"Extended2", type text}, {"Extended3", type text}, {"Extended4", type text}, {"Extended5", type text}, {"Occupational_Interests", type text}, {"Types_Of_Opportunity", type text}, {"YearOutStudent", Int64.Type}, {"FullyRegistered", Int64.Type}, {"Country_Of_Domicile", type text}, {"Skills", type text}, {"Languages", type text}, {"Career_Readiness", type text}, {"Career_Readiness_Code", type text}, {"Number_Dependents", type text}, {"CareerObjective", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Student_Active", "Student_Details", "Faculty", "Department_Code", "Course_Code", "Last_Modified", "Created_Date", "Unknown_Interests", "Blocked", "Blocked_Date", "Blocked_Reason", "Student_Location", "JSON_Profile_Complete", "Email_Pref", "Extended1", "Extended2", "Extended3", "Extended4", "Extended5", "Types_Of_Opportunity", "YearOutStudent", "FullyRegistered", "Skills", "Languages", "Number_Dependents", "CareerObjective"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if Text.StartsWith([Disability], "00") then "No" else if Text.StartsWith([Disability], "0") then "Yes" else if Text.StartsWith([Disability], "10") then "Yes" else if Text.StartsWith([Disability], "5") then "Yes" else if Text.StartsWith([Disability], "95") then "No" else if Text.StartsWith([Disability], "96") then "Yes" else if Text.StartsWith([Disability], "98") then "Prefer not to say" else null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"User_Id", "Student_Number", "Fee_Status", "Nationality", "Ethnicity", "Disability", "Custom", "YearOfStudy", "Type", "Department", "Course", "Course_Start", "Course_End", "Daily_Job_Alerts", "Weekly_Job_Alerts", "Daily_Event_Alerts", "Weekly_Event_Alerts", "Bulk_Emails", "Last_Login_Date", "Gender", "Student_Tag", "Occupational_Interests", "Country_Of_Domicile", "Career_Readiness", "Career_Readiness_Code"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Disability", "Disability Type"}, {"Custom", "Disability"}}),
#"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Disability Type", Splitter.SplitTextByPositions({0, 4}, false), {"Disability Type.1", "Disability Type.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Disability Type.1", type text}, {"Disability Type.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Disability Type.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Disability Type.2", "Disability Type"}, {"Type", "Type of Study"}}),
#"Split Column by Position1" = Table.SplitColumn(#"Renamed Columns1", "Course", Splitter.SplitTextByPositions({0, 3}, false), {"Course.1", "Course.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Course.1", type text}, {"Course.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Course.1", "FT/PT"}, {"Course.2", "Programme"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Daily_Job_Alerts", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type3","1","Yes",Replacer.ReplaceText,{"Daily_Job_Alerts"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","0","No",Replacer.ReplaceText,{"Daily_Job_Alerts"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value1",{{"Weekly_Job_Alerts", type text}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type4","1","Yes",Replacer.ReplaceText,{"Weekly_Job_Alerts"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","0","No",Replacer.ReplaceText,{"Weekly_Job_Alerts"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value3",{{"Daily_Event_Alerts", type text}}),
#"Replaced Value4" = Table.ReplaceValue(#"Changed Type5","true","Yes",Replacer.ReplaceText,{"Daily_Event_Alerts"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","false","No",Replacer.ReplaceText,{"Daily_Event_Alerts"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value5",{{"Weekly_Event_Alerts", type text}}),
#"Replaced Value6" = Table.ReplaceValue(#"Changed Type6","true","Yes",Replacer.ReplaceText,{"Weekly_Event_Alerts"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","false","No",Replacer.ReplaceText,{"Weekly_Event_Alerts"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Replaced Value7",{{"Bulk_Emails", type text}}),
#"Replaced Value8" = Table.ReplaceValue(#"Changed Type7","1","Yes",Replacer.ReplaceText,{"Bulk_Emails"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","0","No",Replacer.ReplaceText,{"Bulk_Emails"}),
#"Changed Type8" = Table.TransformColumnTypes(#"Replaced Value9",{{"Last_Login_Date", type date}, {"Course_End", type date}, {"Course_Start", type date}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type8", {{"Student_Tag", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Student_Tag"),
#"Changed Type9" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Student_Tag", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type9",{{"Student_Tag", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Trimmed Text", {{"Occupational_Interests", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Occupational_Interests"),
#"Changed Type10" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Occupational_Interests", type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type10",{{"Occupational_Interests", Text.Proper, type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Capitalized Each Word", "Country_Of_Domicile", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Country_Of_Domicile.1", "Country_Of_Domicile.2"}),
#"Changed Type11" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Country_Of_Domicile.1", type text}, {"Country_Of_Domicile.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type11",{"Country_Of_Domicile.2"})
in
#"Removed Columns2"

[audreygerred]

Hi! I would just finish creating the Excel you have, save it to Sharepoint/OneDrive/or load it to your datawarehouse then load it to your model and do a join to be able to utilize the proper naming you want.

[Vectorize]

Thank you for your response. I should have mentioned that my data is coming in via an Odata feed, so I cannot amend it at source before it does into Power BI.