Distribute data onto 3 columns

[sdhn]

Hi All

I am using Power Bi Desktop.

I am importing data in excel or csv format.  One table has a column called “Description”.

Single Description column has three types of data as an example:

FBI FLASH - Increase in PYSA Ransomware Targeting Education Institutions –

Color Code: White

DATE(S)/Time ISSUED: 3/16/2021

We want to separate in three columns in Dashboard.

Color Code	DATE(S)/Time ISSUED	Subject
White	3/16/2021	FBI FLASH - Increase in PYSA Ransomware Targeting Education Institutions –

Please advise. I will highly appricate it.

[Anonymous · Accepted solution]

Hi @sdhn ,

I created a sample pbix file (see attachment) for you, please check whether that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnPyVHDzcQz2UNBV8MxLLkpNLE5VyMxTCIgMdlQISswrzs8tTyxKVQhJLEpPLcnMS1dwTSlNTizJzM8Dqi8uySwpBbGLFR41TFaK1YlWcs7PyS9ScM5PSbVSCM/ILEkFi7o4hrhqBGvqh2Tmpip4BgeHurpYKRjrG5rpGxkYGWJodMpJTM7GrdFE39AYoRHZCy6pxTj9oBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Color Code", each if Text.StartsWith([Data],"Color Code") then [Data] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DATE(S)/Time ISSUED", each if Text.StartsWith([Data],"DATE(S)/Time ISSUED") then [Data] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Subject", each if not Text.StartsWith([Data],"Color Code") and ( not Text.StartsWith([Data],"DATE(S)/Time ISSUED")) then [Data] else null)
in
    #"Added Custom2"

Best Regards

[HashamNiaz]

Hi !

Please use Power Query (M) o split Description column into 3 columns. From Home tab you can go to Transform daat. select the Desciption column, and then choose Split column transformation based on your scenario. Additionally you can use create new column from example transformation & enter few rows to get the whole column populated, you can do this for other 2 remaning columns.

Regards,

Hasham