Split columns in a diffrent number or them depending of its content

[hector3315]

I have the following problem:

I need to split this column in order to get the oks, warnings, errors, nuevos and actualizados:

I will need 5 columns but the fact is that i won't get them classified spliting by delimitator and neither by size.

Any suggestion ?

I have them splited by # but i need to have a colum with all the oks other with all the warnings ... and i don't know how to do it

[v-yulgu-msft · Accepted solution]

Hi @hector3315,

In your scenario, please follow below steps in query editor after importing data into Power BI:
Add index column -> Split column and conbine all columns into a single one -> Add cotegory column -> Pivot column

Power Query reference：

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yulgu\Desktop\test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Text.Split([Column1], "#")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if Text.Contains([Custom], "OK") then "OK" else if Text.Contains([Custom], "ERROR") then "ERROR" else if Text.Contains([Custom], "WARNING") then "WARNING" else if Text.Contains([Custom], "NUEVOS") then "NUEVOS" else "ACTUALIZADOS" ),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom.1]), "Custom.1", "Custom")
in
    #"Pivoted Column"

If you still have any question, please feel free to ask.

Thanks,
Yuliana Gu

[Baskar]

Cool , 

Go to Query Editor , there u can find "Split Column" , that will help u .

 let me know if any help friend 

[hector3315]

I did that at first and this is what i can obtain:

But i need each type in a different column. Here is the problem...

[Baskar]

Do u want Error in one column and OK in one column ?

if yes for that u have to create condition column for that, from this column.

I don't know y u want like that ?

[hector3315]

Yes I want 5 columns: ok, warning, error, actualizados y nuevos and all the values of each row in its correct place.

Could i solve that with the condition column ?

[Baskar]

now u r getting the five column based on which position it was in the single cell.

Condtion column am not sure . I suggest to try with @ImkeF idea.

[ImkeF]

I'd suggest the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ve2MlB2LSrKL7IyVNJRMlSK1QELGio7JpeUWhlB5QyAckZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1], "#")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom","Custom",Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),{"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Custom.1]), "Custom.1", "Custom.2")
in
    #"Pivoted Column"