Forum Discussion
Helper Vseparate values from two columns at the same time power query
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.
Super UserHi 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"
Helper VThanks, but my source, several hundred thousand rows, does not come as your Excel file. The first image shows how I receive the values.
v-lgarikapatCommunity Support
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
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
- v-lgarikapat
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