separate values from two columns at the same time power query

[telesforo1969]

I appreciate the support for separating values from two columns at the same time as indicated in the image, I am using the function Splitter.SplitTextByCharacterTransition. The image in Excel shows the source and the transformation.

[v-lgarikapat]

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

[MohamedFowzan1]

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"

[telesforo1969]

Thanks, but my source, several hundred thousand rows, does not come as your Excel file. The first image shows how I receive the values.

[Ashish_Mathur]

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([Tipo],":"),Text.Split([Mezcla],":")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    Custom1 = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Custom", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"TipoTransformado", "MezclaTransformado"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tipo", type text}, {"Mezcla", type text}, {"TipoTransformado", type text}, {"MezclaTransformado", Int64.Type}})
in
    #"Changed Type"

Hope this helps.

[telesforo1969]

Thank you very much. I'll reply and let you know.

[shashiPaul1570_]

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)

[telesforo1969]

Thank you. I'm going to try it and I'll let you know.

[shashiPaul1570_]

In addition to my above reply, 
this M code will transform

Tipo     | Mezcla
-------- | --------
A:B      | 60:40

into
TipoTransformado | MezclaTransformado
---------------- | -------------------
A | 60
B | 40

[telesforo1969]

I have already made all the possible combinations to expand and I am not getting the expected result.   I did step 1 with the Splitter.SplitTextByDelimiter function, is there any downside?

[shashiPaul1570_]

Hi @telesforo1969 ,

Thank you for sharing your solution. 
I have tried your solution and it is giving me the desired output with the following M code. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\firstname.lastname\Downloads\PQ testing.xlsx"), null, true),  
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column A", type text}, {"Column B", type text}}),

    // Split Column A and Column B into lists
    AddListA = Table.AddColumn(#"Changed Type", "ListA", each Text.Split([Column A], ":")),
    AddListB = Table.AddColumn(AddListA, "ListB", each Text.Split([Column B], ":")),

    // Zip the two lists together
    AddZipped = Table.AddColumn(AddListB, "Zipped", each List.Zip({[ListA], [ListB]})),

    // Expand Zipped list to rows
    ExpandedZipped = Table.ExpandListColumn(AddZipped, "Zipped"),

    // Convert each list pair into a record with named fields
    ToRecords = Table.TransformColumns(ExpandedZipped, {"Zipped", each Record.FromList(_, {"TipoTransformado", "MezclaTransformado"})}),

    // Expand the record into separate columns
    ExpandedColumns = Table.ExpandRecordColumn(ToRecords, "Zipped", {"TipoTransformado", "MezclaTransformado"}),

    // Convert MezclaTransformado to number
    ChangedTypes = Table.TransformColumnTypes(ExpandedColumns,{{"MezclaTransformado", Int64.Type}})
in
    ChangedTypes

Please try it accordingly and share your outcome. 

Thanks

Shashi Paul 

[Greg_Deckler]

@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.

[telesforo1969]

Thank you for the support. I will try and let you know.