Spilt the Columns in Power bi Table

[Mahamood0218]

Hi Experts,

I have a PBI Table  Active Table with two coulmns(Action_ID and Action_Reactive ) my requirement is by using "Active Table" i want to create Dimension table along with New Column as Test Result . In Test Result i want to show "Action_Reactive" Coulmn field data as Vol and Invol. In Action_Reactive column i have a data like Material,Vol-Notice ,Invol-Coat . So my Requirement is i want display "Test Result'' data      Vol-Notice  as Vol                                                                                                                                                                                        Invol-Coat as Invol                                                                                                                                                                                     Material as    " " (Blank)                                                                                                                                       For Referal please check below screenshot

[Mahamood0218]

@Vijay_A_Verma @wdx223_Daniel @Anonymous  hi i have created dimension table for split data

could you please sujjest new column(Term_Text coumn ) for display vol,invol,and blank data.

[Vijay_A_Verma]

Method will stay the same. There is no change in that. 

[Mahamood0218]

@Vijay_A_Verma in above pic i have created Dimension table so we can try with coding is it possible?

[Mahamood0218]

@Vijay_A_Verma  when i try with as your sujjestion data is divided (i got the my requirment) but when i apply and close in transform edit mode again it shows the below error 

so i have a another technique by using dimension table we can summarize all columns into single unit then we can divide vol and invol from table

DimAR = ADDCOLUMNS(SUMMARIZE(TextDetails,[action_id],[action_reactive]), "TestResult", IF(FIND("-",[action_reactive],1,0) > 0, LEFT([action_reactive],
FIND("-",[action_reactive],1,0)-1)))

above code will work as per our requirment or do u have any idea ,pls suggest me 

Thanks&Regards

[Vijay_A_Verma]

Just follow the step give by me and code will be automatically generated - You can select Action_Reasoncolumn - In PQ, Add column menu - Extract - Text before delimiter - Put "-" without quotes in Delimiter and under Advanced options - Select from the end of the input

If you need step, use this where Source will have to be replaced with your previous step

= Table.TransformColumns(Source, {{"Action_Reason", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}})

[Mahamood0218]

if i used above code and previous method after that when i close and save in edit query i will be getting erro like below

that is main reason i have created dimension table and it is possible by applying if else statement for spilt the data?

[Vijay_A_Verma]

In a custom column, put following formula

= if Text.Contains([Action_Reason],"-") then Text.Split([Action_Reason],"-"){0} else ""

[wdx223_Daniel]

NewStep=Table.AddColumn(PreviousStepName,"test",each List.Reverse(Text.Split([Action_Reactive],"-")){1}?)

[Mahamood0218]

@wdx223_Daniel could you please suggest step by step

[Mahamood0218]

Hi 

@Vijay_A_Verma @Anonymous  now i am getting the split data

but when i close and apply button in Transform editor i am getting issue like below 

Could you please suggest me

[Vijay_A_Verma]

Do you have 64 bit of Power BI installed? This you can see in Help - About.

Another thing you can try is to delete existing query and make the query afresh.

[Vijay_A_Verma]

You can select Action_Reactive column - In PQ, Add column menu - Extract - Text before delimiter - Put "-" without quotes in Delimiter and under Advanced options - Select from the end of the input

[Anonymous]

    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RchLCoAgEADQu8zaoH/dRVwMIjFgGjoF3T5mIlq+Zy10LRjYkUMhjOCMhV7myJH8rR7EIXF5OQqvHJuUmXzQm76rjHxWvfk73NSLmJKMz8h663+U6gnOPQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [action_id = _t, action_reactive = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"action_id", Int64.Type}, {"action_reactive", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "result", each Text.Reverse(Text.AfterDelimiter(Text.Reverse([action_reactive]),"-")))
in
    #"Aggiunta colonna personalizzata"

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RchLCoAgEADQu8zaoH/dRVwMIjFgGjoF3T5mIlq+Zy10LRjYkUMhjOCMhV7myJH8rR7EIXF5OQqvHJuUmXzQm76rjHxWvfk73NSLmJKMz8h663+U6gnOPQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [action_id = _t, action_reactive = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"action_id", Int64.Type}, {"action_reactive", type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "result", each if not Text.Contains([action_reactive],"-") then " " else Text.BeforeDelimiter([action_reactive],"-"))
in
    #"Aggiunta colonna personalizzata"