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.

     

MohamedFowzan1's avatar
MohamedFowzan1
Icon for Super User rankSuper User
1 year ago

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

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

    • v-lgarikapat's avatar
      v-lgarikapat
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi telesforo1969 , The logic you've applied in Power Query (splitting, expanding, indexing, and merging) is absolutely valid and scalable in Power BI.

       

      Best Regards,

      Lakshmi

       

       

      • v-lgarikapat's avatar
        v-lgarikapat
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi telesforo1969 ,

        We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

         

        Best Regards,

        Lakshmi