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.
Hello,
I am having trouble trying to get a numerical list to expand or display from within a column in Power Query.
I would appreciate any help!
Please see attached picture. Thank you!
Solved! Go to Solution.
Try this @rmahmood
It uses Value.Is to determine if it is a list or not, then extracts the numbers. From there you can split columns, or whatever.
let
Source = #table(
{"ID", "Name", "City", "Other"},
{
{123, "Alice", "Wonderand", ""},
{456, "Bob", "Wonderland", {1..10}}
}
),
#"Added Custom" =
Table.AddColumn(
Source,
"Custom",
each
if Value.Is([Other], type list)
then Text.Combine(List.Transform([Other], Text.From), ",")
else [Other]
)
in
#"Added Custom"
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 ReportingTable.SplitColumn(PreviousStepName,"Training Modules",each if _ is list then _ else {_},List.Max(List.Transform(PreviousStepName[Training Modules],each if _ is list then List.Count(_) else 1)))
Hi,
This is actually coming from the source.
Try this @rmahmood
It uses Value.Is to determine if it is a list or not, then extracts the numbers. From there you can split columns, or whatever.
let
Source = #table(
{"ID", "Name", "City", "Other"},
{
{123, "Alice", "Wonderand", ""},
{456, "Bob", "Wonderland", {1..10}}
}
),
#"Added Custom" =
Table.AddColumn(
Source,
"Custom",
each
if Value.Is([Other], type list)
then Text.Combine(List.Transform([Other], Text.From), ",")
else [Other]
)
in
#"Added Custom"
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 Reporting@rmahmood Seems like you are going to need an if statement to check if null, then return null or otherwise, extract values from the list like:
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})
Will be different if you are trying to expand to rows, would be:
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
@ImkeF and @edhans might have other thoughts.
Covering 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.