Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
Proud to be a Super User!
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!
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!
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"
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |