Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
is it possible to combine to list into one table in Power Query
currently I have two lists with the same number of objects inside:
and I would like to have these two merged 1 to 1 to have two columns
for example
groupA
and group B has other 4 values in the same row.
I would like to join them to have 4 rows and 2 columns (one column with group A value and second column with groop B)
is it possible?
thank you!
Solved! Go to Solution.
I think List.Zip might work for you @jesse_james - see this:
So col1 has two lists, 1-4, and 5-8. Col2 has A-D and E-H
This code turns it into this table:
let
Source =
#table(
{"Col1", "Col2"},
{
{{1..4}, {"A".."D"}},
{{5..8}, {"E".."H"}}
}
),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({[Col1], [Col2]})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col1", "Col2"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2"})
in
#"Split Column by Delimiter"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI think List.Zip might work for you @jesse_james - see this:
So col1 has two lists, 1-4, and 5-8. Col2 has A-D and E-H
This code turns it into this table:
let
Source =
#table(
{"Col1", "Col2"},
{
{{1..4}, {"A".."D"}},
{{5..8}, {"E".."H"}}
}
),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({[Col1], [Col2]})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col1", "Col2"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2"})
in
#"Split Column by Delimiter"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.