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.

     

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