Forum Discussion

telesforo1969's avatar
telesforo1969
Helper V
1 year ago
Solved

separate values from two columns at the same time power query

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 t...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

     

shashiPaul1570_'s avatar
shashiPaul1570_
Responsive Resident
1 year ago

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's avatar
telesforo1969
Helper V
1 year ago

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