Dividing a Column

[kjcompton]

Good Afternoon!

I have a report that I am working with, and one of the columns is called INTERNAL_EXTERNAL, and the results for each row populates as EXT__________ or INT __________.

I am curious if I can break these off to two columns, so that one column will show EXT results and one column will show INT results? This will make other reports easier for the end user to read, vs trying to have to filter between INT/EXT they can see one with one or the other.

I appreciate your help!

[ChrisMendoza · Accepted solution]

You can do as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wco0IiVeK1YlW8vSDMlIrSiCMzDwQIxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INTERNAL_EXTERNAL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INTERNAL_EXTERNAL", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EXTERNAL", each if Text.StartsWith([INTERNAL_EXTERNAL],"EXT", Comparer.OrdinalIgnoreCase) then [INTERNAL_EXTERNAL] else ""),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom", each if Text.StartsWith([INTERNAL_EXTERNAL],"EXT", Comparer.OrdinalIgnoreCase) then [INTERNAL_EXTERNAL] else "","",Replacer.ReplaceText,{"INTERNAL_EXTERNAL"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"INTERNAL_EXTERNAL", "INTERNAL"}})
in
    #"Renamed Columns"

[v-kelly-msft · Accepted solution]

Hi  @kjcompton ,

You could also realize it using dax expression:

Create 2 columns as below:

_Int = 
var _search=SEARCH("INT",'Table'[Internal_External],1,BLANK())
Return
IF(_search<>BLANK(),'Table'[Internal_External],BLANK())

_Ext = 
var _search=SEARCH("EXT",'Table'[Internal_External],1,BLANK())
Return
IF(_search<>BLANK(),'Table'[Internal_External],BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[ChrisMendoza · Accepted solution]

You can do as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wco0IiVeK1YlW8vSDMlIrSiCMzDwQIxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INTERNAL_EXTERNAL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INTERNAL_EXTERNAL", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EXTERNAL", each if Text.StartsWith([INTERNAL_EXTERNAL],"EXT", Comparer.OrdinalIgnoreCase) then [INTERNAL_EXTERNAL] else ""),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom", each if Text.StartsWith([INTERNAL_EXTERNAL],"EXT", Comparer.OrdinalIgnoreCase) then [INTERNAL_EXTERNAL] else "","",Replacer.ReplaceText,{"INTERNAL_EXTERNAL"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"INTERNAL_EXTERNAL", "INTERNAL"}})
in
    #"Renamed Columns"