cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hector3315 Frequent Visitor
Frequent Visitor

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

I have the following problem:

 

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

 

asdfs.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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"

5.PNG

 

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

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User
Super User

Re: Split columns in a diffrent number or them

Cool , 

 

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

1.JPG

 

 let me know if any help friend 

 

 

 

 

 

hector3315 Frequent Visitor
Frequent Visitor

Re: Split columns in a diffrent number or them

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

 

Captura.PNG

 

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

Highlighted
Super User
Super User

Re: Split columns in a diffrent number or them

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 Frequent Visitor
Frequent Visitor

Re: Split columns in a diffrent number or them

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 ?

Super User
Super User

Re: Split columns in a diffrent number or them

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Split columns in a diffrent number or them

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.

Community Support Team
Community Support Team

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

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"

5.PNG

 

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

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.