Forum Discussion
Dividing a Column
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!
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"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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
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"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,
KellyDid I answer your question? Mark my post as a solution!