Forum Discussion

telesforo1969's avatar
telesforo1969
Icon for Helper V rankHelper 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_
Icon for Responsive Resident rankResponsive 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)

shashiPaul1570_'s avatar
shashiPaul1570_
Icon for Responsive Resident rankResponsive Resident
1 year ago

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

    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_'s avatar
      shashiPaul1570_
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      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