separate values from two columns at the same time power query

telesforo1969 One way to do this is to import the table twice and in both split the columns by ":". Then you simply remove the first two columns for Tipo and Mezcla in one query, remove the second two columns for Tipo and Mezcla in the other. Rename the columns and append. Like this:

Table query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRyUtJRMjOwMjFQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tipo = _t, Mezcla = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tipo", type text}, {"Mezcla", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByCharacterTransition( { "A".."z" }, { ":" } )( [Tipo] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Tipo", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Tipo.1", "Tipo.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tipo.1", type text}, {"Tipo.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Mezcla", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Mezcla.1", "Mezcla.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Mezcla.1", Int64.Type}, {"Mezcla.2", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Tipo.2", "Mezcla.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Tipo.1", "Tipo"}, {"Mezcla.1", "Mezcla"}})
in
    #"Renamed Columns"

Table (2) query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRyUtJRMjOwMjFQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tipo = _t, Mezcla = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tipo", type text}, {"Mezcla", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Tipo", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Tipo.1", "Tipo.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tipo.1", type text}, {"Tipo.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Mezcla", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Mezcla.1", "Mezcla.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Mezcla.1", Int64.Type}, {"Mezcla.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Tipo.1", "Mezcla.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Tipo.2", "Tipo"}, {"Mezcla.2", "Mezcla"}})
in
    #"Renamed Columns"

Final table:

let
    Source = Table.Combine({Table, #"Table (2)"})
in
    Source

You can then set the Table and Table (2) queries to not load.

Hi telesforo1969 
Thanks for sharing your question
You're on the right track using Splitter.SplitTextByCharacterTransition, but for this specific transformation where two delimited columns (as in excel sheet) must be split in parallel and expanded row-wise, you should try this

Here's how to do it step-by-step

1. Split each column into a list using Text.Split.

2. Zip the two resulting lists together using List.Zip so each item is matched correctly.

3. Expand the combined list into rows and then into separate columns.

Here is the M code 

let
    Source = YourTableName,
    AddTipoList = Table.AddColumn(Source, "TipoList", each Text.Split([Tipo], ":")),
    AddMezclaList = Table.AddColumn(AddTipoList, "MezclaList", each Text.Split([Mezcla], ":")),
    CombineLists = Table.AddColumn(AddMezclaList, "Combined", each List.Zip({[TipoList], [MezclaList]})),
    Expanded = Table.ExpandListColumn(CombineLists, "Combined"),
    ToColumns = Table.TransformColumns(Expanded, {"Combined", each Record.FromList(_, {"TipoTransformado", "MezclaTransformado"})}),
    ExpandedColumns = Table.ExpandRecordColumn(ToColumns, "Combined", {"TipoTransformado", "MezclaTransformado"})
in
    ExpandedColumns

Let me know if you'd like a reusable version of this in a function.
If this helps, ✔ Give a Kudo • Mark as Solution – help others too!

Thanks 🙌
Shashi Paul
(Microsoft Fabric | Power BI Developer)

Hi telesforo1969 

I tested this it works:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Mohamed.fowzan\Desktop\Split.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    ChangedType = Table.TransformColumnTypes(Sheet1_Sheet, {{"Column1", type text}, {"Column2", type text}}),
    PromotedHeaders = Table.PromoteHeaders(ChangedType, [PromoteAllScalars=true]),
    ChangedType1 = Table.TransformColumnTypes(PromotedHeaders, {{"Tipo", type text}, {"Mezcla", type text}}),

    AddRowIndex = Table.AddIndexColumn(ChangedType1, "RowIndex", 0, 1),

    AddTipoList = Table.AddColumn(
        AddRowIndex,
        "TipoList",
        each if Text.Contains([Tipo], ":") then Text.Split([Tipo], ":") else {[Tipo]}, 
        type list),

    AddMezclaList = Table.AddColumn(
        AddTipoList,
        "MezclaList",
        each if Text.Contains(Text.From([Mezcla]), ":") then Text.Split(Text.From([Mezcla]), ":") else {Text.From([Mezcla])}, 
        type list),

    ExpandTipoList = Table.ExpandListColumn(AddMezclaList, "TipoList"),

    ExpandMezclaList = Table.ExpandListColumn(ExpandTipoList, "MezclaList"),

    AddInnerIndexTipo = Table.AddIndexColumn(ExpandTipoList, "InnerIndex", 0, 1),

    AddInnerIndexMezcla = Table.AddIndexColumn(ExpandMezclaList, "InnerIndex", 0, 1),

    Merged = Table.NestedJoin(
        AddInnerIndexTipo, {"RowIndex", "InnerIndex"},
        AddInnerIndexMezcla, {"RowIndex", "InnerIndex"},
        "MezclaTable", JoinKind.Inner
    ),

    ExpandedMerged = Table.ExpandTableColumn(Merged, "MezclaTable", {"MezclaList"}, {"MezclaTransformado"}),

    RenameColumns = Table.RenameColumns(ExpandedMerged, {{"TipoList", "TipoTransformado"}}),
    #"Removed Other Columns" = Table.SelectColumns(RenameColumns,{"Tipo", "Mezcla", "TipoTransformado", "MezclaTransformado"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"TipoTransformado", type text}, {"MezclaTransformado", type text}})
in
    #"Changed Type"

Hi telesforo1969 ,

Thanks for reaching out to the Microsoft fabric community forum.

MohamedFowzan1 ,

shashiPaul1570_ ,

Ashish_Mathur ,

Greg_Deckler 

Thanks for your prompt response

telesforo1969 ,

I just wanted to check if your issue has been resolved. If you still have any questions or need help, feel free to reach out I’m happy to assist.

Thank you for being an active part of the community. Looking forward to hearing from you!

Best regards,
Lakshmi